Quick Comparison

CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

새 테이블의 대리 키는 보통 IDENTITY로 시작합니다. 애플리케이션이 값을 넣을 수 있어야 하는 데이터 이관이면 BY DEFAULT, 일반 INSERT에서 값을 막고 싶으면 ALWAYS를 고릅니다. SERIAL은 같은 sequence 기반 동작을 축약한 기존 문법이며, sequence 값은 롤백돼도 되돌아가지 않으므로 업무 순번으로 쓰지 않습니다.

문법

IDENTITY는 컬럼 정의 안에 자동 증가 규칙을 명시한다

GENERATED ... AS IDENTITY는 자동 증가 컬럼을 표준 SQL에 가까운 형태로 선언합니다. BY DEFAULT는 명시 값을 그대로 받아들이고, ALWAYS는 기본적으로 사용자가 값을 직접 넣지 못하게 합니다. 둘 다 내부 sequence가 다음 값을 공급합니다.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL
);

마이그레이션이나 외부 데이터 복원처럼 기존 ID를 그대로 넣어야 하는 경우가 있으면 BY DEFAULT가 유연합니다. 반대로 애플리케이션이 ID를 직접 지정하면 안 되는 규칙을 강하게 두려면 ALWAYS가 더 명확합니다.

ALWAYS 컬럼에 복원 값을 넣는 작업은 의도를 SQL에 드러내야 합니다. OVERRIDING SYSTEM VALUE가 없으면 명시값 INSERT는 오류가 납니다.

-- GENERATED ALWAYS AS IDENTITY 컬럼에 이관 값을 넣을 때
INSERT INTO orders (id, user_id)
OVERRIDING SYSTEM VALUE
VALUES (1001, 42);

-- BY DEFAULT 컬럼에서 명시값을 무시하고 새 번호를 강제할 때
INSERT INTO users (id, email)
OVERRIDING USER VALUE
VALUES (9999, 'new@example.com');

SERIAL은 sequence와 DEFAULT를 자동으로 만든다

BIGSERIAL은 실제 자료형이 아니라 BIGINT 컬럼, sequence, DEFAULT nextval(...)를 함께 만드는 축약 문법입니다. 기존 PostgreSQL 코드베이스에서 매우 흔하게 쓰입니다.

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

대략 아래처럼 해석할 수 있습니다.

CREATE SEQUENCE posts_id_seq;

CREATE TABLE posts (
  id BIGINT NOT NULL DEFAULT nextval('posts_id_seq') PRIMARY KEY,
  title TEXT NOT NULL
);

Sequence 동작

sequence는 트랜잭션 롤백과 별도로 증가합니다. nextval()로 값을 받은 뒤 트랜잭션이 실패해도 그 번호는 되돌아가지 않습니다. 그래서 자동 증가 ID에는 gap이 생길 수 있습니다.

SELECT nextval('orders_id_seq');
SELECT currval('orders_id_seq');

currval()은 현재 세션에서 그 sequence에 nextval()을 먼저 호출한 뒤에만 값을 돌려줍니다. 다른 세션이 마지막으로 받은 번호를 조회하는 함수가 아닙니다. nextval()과 setval()로 바꾼 값은 롤백해도 되돌아가지 않으므로, 트랜잭션 실패를 테스트할 때도 gap은 정상 동작입니다.

gap 없는 번호가 회계 전표나 송장 번호처럼 비즈니스 규칙이면 일반 PK sequence로 해결하면 안 됩니다. 별도 잠금, 번호 발급 테이블, 발급 확정 시점 설계가 필요합니다.

재시작과 동기화

수동으로 ID를 넣었거나 데이터를 복원한 뒤 sequence 현재값이 실제 최댓값보다 낮으면 다음 INSERT에서 중복 키 오류가 날 수 있습니다. 다른 INSERT가 동시에 실행되지 않는 이관 구간에서, 빈 테이블과 값이 있는 테이블을 모두 처리하도록 다음 값을 맞춥니다.

BEGIN;
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;

SELECT setval(
  'users_id_seq',
  COALESCE((SELECT MAX(id) FROM users), 1),
  EXISTS (SELECT 1 FROM users)
);

COMMIT;

세 번째 인자가 true이면 다음 nextval()은 설정한 값 다음 번호를, false이면 설정한 값 자체를 반환합니다. 따라서 빈 users 테이블은 1, false로 맞춰 다음 INSERT가 1을 받습니다. identity/serial의 실제 sequence 이름을 하드코딩하기 어렵다면 pg_get_serial_sequence('users', 'id')로 먼저 확인합니다.

주의할 점