[PostgreSQL] GENERATED ALWAYS AS IDENTITY — serial이 만든 시퀀스 어긋남 사고를 막는다

설치·접속: PostgreSQL 설치와 접속

부제: serial PK 테이블에서 앱이 실수로 id를 명시 INSERT 해 시퀀스가 뒤처지고 중복 키 에러가 터졌을 때

GENERATED ALWAYS AS IDENTITY는 사용자가 id 값을 직접 넣는 것 자체를 거부한다. id는 오직 DB가 뽑는다.

CREATE TABLE always (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    note text
);

INSERT INTO always (note) VALUES ('auto1');  -- id 자동 1
INSERT INTO always (note) VALUES ('auto2');  -- id 자동 2

-- 앱이 실수로 id를 직접 넣으면
INSERT INTO always (id, note) VALUES (100, 'manual');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

명시 값 자체가 막혔다. 이게 왜 중요한지는 serial과 비교하면 드러난다. serial은 앱이 넣은 명시 값을 그냥 받되 시퀀스를 전진시키지 않아서, 나중에 시퀀스가 그 값을 다시 뽑는 순간 중복 키로 터진다.

CREATE TABLE serial (id serial PRIMARY KEY, note text);

INSERT INTO serial (id, note) VALUES (1, 'manual');  -- 명시 값 1, 시퀀스는 그대로 1
INSERT INTO serial (note)     VALUES ('auto');       -- 시퀀스가 1을 뽑음 → 충돌

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "serial_pkey"
DETAIL:  Key (id)=(1) already exists.

serial은 이 사고를 조용히 방치하다 한참 뒤 운영 중에 터뜨린다. GENERATED ALWAYS는 애초에 명시 INSERT를 거부해 "시퀀스와 실제 값이 어긋나는" 상태를 만들 수 없게 한다.

정당하게 특정 id를 넣어야 하는 경우(데이터 이관 등)엔 OVERRIDING SYSTEM VALUE로 의도를 명시한다. 실수는 막되 명시적 우회는 열어둔 설계다.

INSERT INTO always (id, note) OVERRIDING SYSTEM VALUE VALUES (100, 'forced');
SELECT * FROM always ORDER BY id;

 id  |  note
-----+--------
   1 | auto1
   2 | auto2
 100 | forced

반면 GENERATED BY DEFAULT AS IDENTITY는 사용자 값을 우선하고, 없으면 시퀀스가 뽑는다. serial과 동작이 비슷하지만 표준 SQL 문법이고 시퀀스 소유권이 컬럼에 묶인다는 이점이 있다.

CREATE TABLE bydefault (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, note text);
INSERT INTO bydefault (note)     VALUES ('auto');    -- id 1
INSERT INTO bydefault (id, note) VALUES (50, 'manual');  -- 명시 값 그대로 수용
SELECT * FROM bydefault ORDER BY id;

 id |  note
----+--------
  1 | auto
 50 | manual

정리하면, 사고를 원천 차단하려면 ALWAYS, 이관·복제처럼 값 지정이 잦으면 BY DEFAULT. 둘 다 암묵적으로 NOT NULL이며 serial보다 값 생성 제어가 명확하다.

이렇게도 쓴다

시작값·증가폭 같은 시퀀스 옵션을 인라인으로 준다.

id bigint GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10)

 

기존 serial 컬럼을 identity로 전환한다.

ALTER TABLE t ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

 

BY DEFAULT 테이블에 이관으로 큰 값을 넣은 뒤 시퀀스를 따라잡게 재설정한다.

SELECT setval(pg_get_serial_sequence('bydefault','id'), (SELECT max(id) FROM bydefault));

 

MERGE/upsert의 대상 PK로 써서 신규 행만 안전하게 채번한다. (조합: MERGE)

MERGE INTO always t USING src s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (note) VALUES (s.note);