PostgreSQL에서 id 등을 포함한 record를 수동으로 삽입하다보면 seq가 꼬일 때가 있다. 문제 상황을 이해하고 해결해보자.

재현 방법

CREATE TABLE heka1024
(
  id  SERIAL PRIMARY KEY,
  val TEXT
);

INSERT INTO
  heka1024 (id, val)
VALUES
  (1, 'hello'),
  (2, 'world')

이렇게 매뉴얼하게id와 함께 테이블에 insert를 해보자. 그 후

INSERT INTO
  heka1024 (val)
VALUES
  ('fail!');

id를 지정하지 않고 insert를 시도할 경우 다음과 같은 에러가 발생한다.

[23505] ERROR: duplicate key value violates unique constraint "heka1024_pkey"
Detail: Key (id)=(1) already exists.

이유는 id를 만드는데 사용하는 heka1024_id_seq에서는 마지막 값을 모르고 있기 때문이다.

SELECT
  last_value
FROM
  heka1024_id_seq;

위와 같이 조회할 경우 값이 1이 나오는 걸 볼 수 있다.

해결 방법

문제가 되는 테이블에 다음과 같은 구문을 실행함으로써 해결할 수 있다.

-- Find MAX(id) from table and mark as last value
SELECT SETVAL('heka1024_id_seq', (SELECT MAX(id) FROM heka1024), true);

PostgreSQL은 위에 적었듯 seq를 통해 id의 값을 관리한다. PostgreSQL의 serial 타입은 다음과 동일한 효과를 갖는다.

-- Serial
CREATE TABLE tablename (
    colname SERIAL
);

-- Equiv
CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

serial은 setval을 통해 마지막 값을 마킹할 수 있다. 따라서 처음 제시한 구문은 내 테이블의 가장 큰 id를 찾아 그 id의 다음 값부터 시퀀스를 다시 시작하게 만들어서 문제를 해결하는 것이다.

이제 다시 insert를 시도해보자.

INSERT INTO
  heka1024 (val)
VALUES
  ('success!');

문제없이 잘 되는 걸 볼 수 있다.

참고 문헌

• https://www.postgresql.org/docs/current/functions-sequence.html
• https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL