MySQL vs PostgreSQL 문법 차이 정리

MySQL과 PostgreSQL를 공부하면서 문법 차이로 인한 오류가 많이 발생하였다.
따라서 이번 포스팅은 그 차이를 정리해보고자 작성되었다.

목표: MySQL ↔ PostgreSQL 전환 시 헷갈리는 문법 차이를 한 번에 정리

---

1. 기본 철학 차이

항목	MySQL	PostgreSQL
성격	실용 중심, 관대한 문법	표준(SQL) 준수, 엄격
기본 엔진	InnoDB	단일 엔진
타입 안정성	비교적 느슨	매우 엄격
실무 포지션	웹/서비스 백엔드	데이터 중심, 분석, 금융

---

2. AUTO INCREMENT / SEQUENCE

MySQL

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

PostgreSQL

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY
);

또는

CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

---

3. 날짜/시간 함수

목적	MySQL	PostgreSQL
현재 날짜	CURDATE()	CURRENT_DATE
현재 시각	NOW()	NOW()
날짜 차이	DATEDIFF(a,b)	a - b
포맷	DATE_FORMAT()	TO_CHAR()
날짜 계산	INTERVAL 7 DAY	INTERVAL '7 days'

---

4. IF / CASE / 조건 분기

-- MySQL
SELECT IF(score >= 60, 'PASS', 'FAIL');

-- PostgreSQL
SELECT CASE WHEN score >= 60 THEN 'PASS' ELSE 'FAIL' END;

---

5. 트랜잭션 제어

공통

START TRANSACTION;
-- 작업
COMMIT;
ROLLBACK;

BEGIN;
-- 작업
COMMIT;
ROLLBACK;

START TRANSACTION과 BEGIN 모두 사용 가능

---

차이 포인트

• MySQL: IF 문은 프로시저 안에서만 가능
• PostgreSQL: DO 블록 사용 가능

DO $$
BEGIN
  IF 1 = 1 THEN
    RAISE NOTICE 'OK';
  END IF;
END $$;

---

INSERT 후 생성된 ID 받기

MySQL

INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (1, 'PAID', NOW(), 0);

SET @oid := LAST_INSERT_ID();

PostgreSQL

INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (2, 'CREATED', NOW(), 0)
RETURNING order_id;

---

RETURNING 값을 변수처럼 쓰는 방법 (CTE)

WITH new_order AS (
  INSERT INTO orders (customer_id, status)
  VALUES (1, 'CREATED')
  RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
  new_order.order_id,
  1,
  2,
  9000
FROM new_order;

구분	MySQL	PostgreSQL
방식	별도 함수 호출 (LAST_INSERT_ID())	쿼리 내 포함 (RETURNING)
효율성	쿼리를 두 번 실행	한 번의 쿼리
확장성	마지막 ID 특화	모든 컬럼 반환 가능
표준 여부	MySQL 전용	PostgreSQL 전용

---

6. NULL 처리

기능	MySQL	PostgreSQL
NULL 치환	IFNULL(a,b)	COALESCE(a,b)
NULL 비교	a IS NULL	동일

---

7. 문자열 결합

-- MySQL
SELECT CONCAT(first_name, last_name);

-- PostgreSQL
SELECT first_name || last_name;

---

8. BOOLEAN 타입

• MySQL: BOOLEAN → TINYINT(1)
• PostgreSQL: BOOLEAN 타입 존재 (TRUE / FALSE)

---

9. GROUP BY 규칙 차이 (중요)

PostgreSQL은 SELECT에 나온 모든 비집계 컬럼을 GROUP BY에 포함해야 한다.

---

10. UPSERT

-- MySQL
INSERT INTO users (email)
VALUES ('a@test.com')
ON DUPLICATE KEY UPDATE email = 'a@test.com';

-- PostgreSQL
INSERT INTO users (email)
VALUES ('a@test.com')
ON CONFLICT (email)
DO UPDATE SET email = EXCLUDED.email;

---

11. VIEW / MATERIALIZED VIEW

• MySQL: VIEW만 지원
• PostgreSQL: MATERIALIZED VIEW 지원

---

12. JSON 처리

-- MySQL
SELECT JSON_EXTRACT(data, '$.name');

-- PostgreSQL
SELECT data->>'name';

---

13. DISTINCT ON (PostgreSQL 전용)

SELECT DISTINCT ON (c.customer_id)
  c.customer_id, c.name, o.order_id, o.ordered_at
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.ordered_at DESC;

---

14. UPDATE + JOIN 차이

-- MySQL
UPDATE orders o
JOIN customers c ON ...
SET o.status = 'X';

-- PostgreSQL
UPDATE orders o
SET status = 'X'
FROM customers c
WHERE o.customer_id = c.customer_id;

---

15. ENUM 처리 차이

• MySQL: ENUM 컬럼
• PostgreSQL: TEXT + CHECK 제약조건

---

PostgreSQL vs MySQL

DDL / DML / DCL / TCL 차이 이해 (추가 정리)

---

16. DDL (Data Definition Language)

DDL 트랜잭션 차이

BEGIN;
CREATE TABLE test (...);
ROLLBACK;

• PostgreSQL: DDL도 트랜잭션
• MySQL: DDL 자동 커밋

---

17. DML (Data Manipulation Language)

• PostgreSQL: RETURNING 적극 활용
• MySQL: 세션 함수 기반

---

18. DCL (권한 관리)

PostgreSQL은 DB → Schema → Table 단계별 권한이 필요하다.

GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
-- 현재까지 있는 테이블들에 대한 권한 --
GRANT ALL ON ALL TABLES IN SCHEMA public TO app;
-- 앞으로 만들어질 테이블들에 대한 권한 추가 부여 --
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app;

# 1) `GRANT CONNECT ON DATABASE shop TO app;`
* `app` 롤이 shop 데이터베이스에 접속할 수 있는 권한을 부여함.
* PostgreSQL은 세션당 DB 1개이므로, CONNECT가 없으면 아예 접속이 막힘.

## 없으면 어떤 일이 생기나

psql -h localhost -U app -d shop

결과:
* `FATAL:  permission denied for database "shop"`

# 2) `GRANT USAGE ON SCHEMA public TO app;`
* `app`이 `public` 스키마 안의 객체를 “볼 수/참조할 수” 있게 해줌.
* 스키마 권한의 `USAGE`는 디렉터리(폴더) 접근 권한에 비유하면 됨.

  * 폴더에 들어갈 수 있어야 그 안의 파일(테이블)을 읽을 수 있음

## 없으면 어떤 일이 생기나

* 테이블에 SELECT 권한을 줬더라도, 스키마 USAGE가 없으면 접근이 막힐 수 있음.
* 전형적인 에러:

  * `ERROR: permission denied for schema public`

## 주의

* `USAGE`는 테이블을 읽게 해주는 권한이 아님.
* 단지 스키마 경로 접근(네임스페이스 접근)임. 실제 테이블 권한은 별도로 필요함.

# 3) `GRANT ALL ON ALL TABLES IN SCHEMA public TO app;`
* 현재 시점에서 `public` 스키마에 이미 존재하는 모든 테이블에 대해 `app`에게 권한을 부여함.
* `ALL`은 테이블에 대해 보통 아래 권한 세트를 의미함:
  `SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER`

> 즉, “지금 존재하는 테이블은 app이 다 쓸 수 있게 하겠다.”

## 없으면 어떤 일이 생기나

* app이 shop DB에 접속은 되는데 테이블 조회가 안 됨
  * `ERROR: permission denied for relation customers`

# 4) `ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app;`
* 앞으로 `public` 스키마에서 새로 생성될 테이블에 대해,
* 기본 권한(default ACL)을 바꿔서 `app`에게 자동으로 권한을 주도록 설정함.

> 즉, “미래에 만들어질 테이블도 app이 자동으로 쓸 수 있게 하겠음.”

## 없으면 어떤 일이 생기나 (가장 흔한 운영 장애)

* 배포/마이그레이션 과정에서 새 테이블이 생김
* 기존 테이블은 잘 되는데 새 테이블만 permission denied

  * `ERROR: permission denied for relation new_table`

## 가장 중요한 함정: “누가 테이블을 만들었는가(소유자) 기준”

`ALTER DEFAULT PRIVILEGES`는 ‘명령을 실행한 롤(소유자 역할)’이 앞으로 만들 객체에만 적용됨.

예를 들어,

* `postgres`로 위 명령을 실행했다면
  → postgres가 생성하는 테이블에만 app 권한이 자동 적용
* 그런데 실제 배포에서 `migrator`라는 계정이 테이블을 만들면
  → postgres의 default privileges가 적용되지 않습니다

# 실습

## 1) app으로 접속 가능한지

psql -U app -d shop -h localhost

## 2) 테이블 목록 보이는지 (스키마 USAGE 체크)

\dt public.*

## 3) 기존 테이블 SELECT 되는지
SELECT COUNT(*) FROM customers;

## 4) “새 테이블”이 자동으로 되는지 (default privileges 체크)

관리자(테이블 생성자)로:
CREATE TABLE public.priv_check (id int);

app으로:
SELECT * FROM public.priv_check;

---

19. TCL (Transaction Control Language)

구분	MySQL	PostgreSQL
DML	트랜잭션	트랜잭션
DDL	자동 커밋	트랜잭션

---

20. 인프라 엔지니어 관점 결론

• MySQL: 빠른 개발, 단순 운영
• PostgreSQL: 장기 운영, 명확한 권한, 사고 최소화