MySQL과 PostgreSQL Query 차이점 정리
MySQL과 PostgreSQL Query의 차이점을 정리해본다. (상시 업데이트)
참고 : 전문적인 내용보다는 참고 용도로 정리하고 있다.
MySQL의 database == PostgreSQL의 schema
데이터베이스 구조
MySQL : database -> table
PostgreSQL : database -> schema -> table
테이블의 집합이라는 의미로 MySQL에서는 database, PostgreSQL에서는 schema가 사용된다.
PostgreSQL의 database
PostgreSQL은 하나의 데이터베이스를 기준으로 접속한다. 접속한 데이터베이스명을 표시한다.
PostgreSQL의 schema
PostgreSQL에서 schema를 지정하지 않으면 public을 기본으로 사용한다.
search_path는 schema 탐색 범위를 의미한다.
기본 정보 확인 쿼리
| 쿼리 설명 | MySQL | PostgreSQL |
|---|---|---|
| 데이터베이스(schema) 확인 | show databases; | \dn |
| 테이블 확인 | show tables; | \dt |
참고 : PostgreSQL에서 쿼리로 확인하려면 information_schema를 활용하면 된다.
-- \dt를 쿼리문으로 변환
SELECT table_name, table_schema, table_type
FROM information_schema.tables
WHERE table_schema IN ('public');
테이블 생성 시 증가하는 컬럼 설정 방법
MySQL은 AUTO_INCREMENT 속성 / PostgreSQL은 serial 타입으로 지정
참고 : Using PostgreSQL SERIAL To Create Auto-increment Column
-- MySQL
CREATE TABLE table_name(
id INT NOT NULL AUTO_INCREMENT,
...
-- PostgreSQL
CREATE TABLE table_name(
id SERIAL,
...
PostgreSQL 은 정수 / 정수를 정수로 계산한다.
타입 캐스트 또는 하나를 실수로 바꾸면 소수점 계산이 가능하다.
SELECT 100 / 3;
-- 해결방법
SELECT CAST(100 AS float) / 3;
SELECT 100.0 / 3;
문자열을 숫자 타입으로 변환하는 방법
MySQL은 정수(Integer)타입으로 변환 시 SIGNED(-포함) 혹은 UNSIGNED 타입을 요구한다.
실수 타입은 DECIMAL 타입으로 하고 자리 수 설정까지 해야 한다. PostgreSQL은 같은 용도로 Numeric 타입이 있다.
-- INTEGER
SELECT CAST('777' AS UNSIGNED);
SELECT CAST('-777' AS SIGNED);
-- FLOAT(DOUBLE)
-- DECIMAL(M, D) M은 총 자리 수 D는 소수 점 자리수를 의미한다.
SELECT CAST('77.77' as DECIMAL(4, 2));
-- INTEGER
SELECT CAST('777' AS INTEGER);
SELECT '-777'::INTEGER;
-- FLOAT(DOUBLE)
SELECT CAST('77.77' AS FLOAT);
SELECT CAST('77.77' AS DOUBLE PRECISION);
SELECT CAST('77.77' as NUMERIC(4, 2));
MySQL 은 문자열을 숫자 타입으로 변환 시 오류를 알려주지 않는다.
PostgreSQL은 오류를 바로 나타내는 반면, MySQL은 값을 0으로 바꿔버려서 주의가 필요하다.
참고 : Hive는 NULL로 표현한다고 한다.
-- MySQL
SELECT CAST('DBDFD' AS SIGNED); -- => 0
MySQL 은 문자열 비교 시 case-insensitive하다.
case-insensitive는 대소문자를 구분하지 않는다는 의미이다. LIKE를 사용할 때도 MySQL과 PostgreSQL 방식이 다르다.
SELECT 'Hello' = 'hello' -- => True
-- PostgreSQL => case-sensitive
SELECT 'Hello' = 'hello' -- => False
PostgreSQL 은 작은 따옴표 / 큰 따옴표 사용을 명확히 구분
작은 따옴표(single quote)는 string을 표현하고 큰 따옴표(double quotes)는 컬럼명과 테이블명 같은 identifier 네이밍에 활용된다.
-- MySQL
SELECT "HELLO"; -- => HELLO
-- PostgreSQL
SELECT "HELLO"; -- => Syntax Error
SELECT 'HELLO'; -- => HELLO
SELECT 'HELLO' AS "Postgres String";
문자열 안에 작은 따옴표가 들어가야 할 경우, ’‘ 형태로 사용하면 된다.
-- PostgreSQL
SELECT 'yahwang''s blog'; -- => yahwang's blog
-- MySQL - 큰 따옴표 활용 가능
SELECT "yahwang's blog";
SELECT 'yahwang''s blog';
PostgreSQL은 기본적으로 모든 identifier를 lower-case(소문자)로 인식한다.
테이블명이나 컬럼명에 대문자가 있다면 “first_Name”처럼 큰 따옴표(double quotes)를 사용해야 한다. (생성할 때도 포함)
PostgreSQL 은 오른쪽 공백이 들어간 문자를 다르게 인식한다.
참고 : MySQL에서 ‘a’ = ‘a ‘가 true로 평가된다? - 우아한형제 기술블로그
-- MySQL
SELECT 'hello' = 'hello '; -- => True
-- PostgreSQL
SELECT 'hello' = 'hello '; -- => False
MySQL 은 HAVING 절에 ALIAS를 허용한다.
표준 SQL에서는 SELECT보다 GROUP BY, HAVING 연산이 먼저 수행한다. ( the logical order of processing )
그래서, ALIAS를 허용하지 않는 것이 원칙이다. 그러나, GROUP BY 절에서는 둘 다 ALIAS를 사용할 수 있다.
단, HAVING 절은 MySQL만 사용 가능하다. ( MySQL에서는 HAVING 절의 ALIAS가 필수인 듯 )
-- MySQL
SELECT EXTRACT(MONTH FROM date) as month, SUM(sales) as total_sum
FROM sample
GROUP BY EXTRACT(MONTH FROM date)
HAVING month > 2;
-- HAVING EXTRACT(MONTH FROM date) > 2; 은 오류 발생
-- PostgreSQL
SELECT EXTRACT(MONTH FROM date) as month, SUM(sales) as total_sum
FROM sample
GROUP BY EXTRACT(MONTH FROM date)
HAVING EXTRACT(MONTH FROM date) > 2;
주의할 점은 PostgreSQL의 HAVING 절을 MySQL에 사용하면 date 컬럼을 오히려 인식하지 못하는 오류가 생길 수 있다. ( 정확한 이유는 찾지 못함 )
참고 : MySQL Handling of GROUP BY
MySQL 특정 함수
IF 함수
CASE WHEN 대신 SELECT 절에 활용 가능 ( 쿼리문이 간결해지는 효과 )
-- MySQL에만 IF문이 존재
SELECT IF(5-3 > 0, 'TRUE', 'FALSE');
-- PostgreSQL
SELECT CASE WHEN 5-3 > 0 THEN 'TRUE' ELSE 'FALSE' END;
IFNULL 함수
IFNULL은 추가 인자로 한 개만 가능 / PostgreSQL에서는 COALESCE 함수로 사용
IFNULL은 첫번째 인자가 NULL이라면 다음 인자값을 리턴하는 의미
( COALESCE는 NULL이 아닌 값이 처음 나오는 값을 리턴하므로 같은 함수는 아니다. )
-- MySQL에만 IFNULL 문이 존재
SELECT IFNULL(NULL, 'IS NULL');
-- PostgreSQL
SELECT COALESCE(NULL, 'IS NULL');
References :