PostgreSQL 시퀀스 생성 방법, sequence 주의사항

postgresql 시퀀스 생성 방법, 시퀀스명 길이제한 주의사항 정리

시퀀스 뿐 아니라 DB 오브젝트 생성을 할때 여러 옵션들을 줄 수 있는데 블로그 글 말고 공식문서를 참고하는게 제일 정확하다.

항상 docs를 보는 습관을 가져야 하며, 이 글은 아래 postgresql sequence docs를 참고했으며 운영상 주의사항을 약간 추가했다.

postgresql 시퀀스 생성하기

 

---

sequence 생성 방법

https://www.postgresql.org/docs/current/sql-createsequence.html

CREATE SEQUENCE

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

※ [ ] 대괄호는 optional 선택사항이다. 지정하지 않으면 default 값으로 들어가게 된다.

---

sequence 주요 옵션 설명

•  data_type (optional)
  • smallint, integer, bigint를 지정 할 수 있다.
  • default는 bigint 이며, bigint는 8bytes로 -9223372036854775808 to 9223372036854775807 숫자를 표현 할 수 있다.
• INCREMENT (optional)
  • 시퀀스 증가값을 지정한다.
  • default는 1이다.
• MINVALUE (optional)
  • 시퀀스가 생성 가능한 가장 작은 값을 정의한다.
  • CYCLE 옵션을 주었을때 시퀀스는 MAXVALUE 도달 시 MINVALUE 부터 재시작한다.
  • default는 오름차순 시퀀스에서는 1이고, 내림차순 시퀀스에서는 data type으로 표현 가능한 가장 낮은 값이다.
• MAXVALUE (optional)
  • 시퀀스의 최대값을 정의한다.
  • default는 오름차순 시퀀스에서는 data type으로 표현 가능한 가장 큰 값이고, 내림차순 시퀀스에서는 -1이다.
• START
  • 시퀀스 시작값을 지정한다.
  • default는 오름차순 시퀀스에서는 minvalue, 내림차순 시퀀스에서는 maxvalue이다.
• CACHE (optional)
  • 시퀀스 캐시 개수를 정의한다.
  • 빠른 시퀀스 접근을 위해 메모리에 지정된 개수만큼 미리 캐싱해 놓는다.
  • ※ DB가 재기동 되거나 모종의 이유로 캐시된 시퀀스가 없어질 수있다.
  • ※ 오름차순,유일값은 정의하지만 순차성은 보장하지 못한다.
  • ※ ex) cache 20 설정 시 : 1,20,2,21,3,22,4,23,5,24,6,25 ... 순으로 저장이 될 수 있다.
  • default는 1로, 캐싱하지 않는다.
• CYCLE / NOCYCLE (optional)
  • 시퀀스를 전부 사용 했을때 처음부터 반복할 건지 정의한다.
  • default는 NO CYCLE로, maxvalue 도달 시 에러가 난다.
• OWNED BY tablename.column_name / OWNED BY NONE (optional)
  • 테이블의 특정 컬럼과 시퀀스를 매핑 할 수 있다.
  • 이 옵션으로 매핑을 하게 되면 테이블의 컬럼 삭제 시 매핑된 시퀀스도 같이 삭제된다.
  • default는 OWNED BY NONE이다.

---

시퀀스명 길이제한 주의사항

시퀀스명의 최대 길이는 63글자다. 64글자를 초과하면 아래와 같이 뒤에문자들이 truncate 되고 생성된다.

생성에 실패해야 되는거 아닌지=.=; 시퀀스명을 테이블명_컬럼명으로 하려다가 64글자를 초과하는 케이스가 생겨서 변경하였다.

postgres=# create sequence test_sq_123456789123456789123456789123456789123456789123456789123456789123456789123456789 start 1;
NOTICE:  identifier "test_sq_123456789123456789123456789123456789123456789123456789123456789123456789123456789" will be truncated to "test_sq_1234567891234567891234567891234567891234567891234567891"

https://www.postgresql.org/message-id/9DE48F36-C0CB-4770-985F-0AF5E113737E%40gmail.com

Re: identifier will be truncated

위 mailing list 를 보면, postgresql 엔진 컴파일 시 limit을 늘리던지(비추천), object name을 줄이라고 한다.

시퀀스 뿐아니라 table, column 등등 64글자를 넘으면 뒤에 자리가 truncate 되서 생성되니 놓치지 않도록 주의가 필요하다.

---

시퀀스 생성 해보기

postgres=# create sequence test_sequence_01
increment 1
minvalue 1
no maxvalue
start 1
cache 20
no cycle
;
CREATE SEQUENCE

postgres=# select nextval('test_sequence_01');
 nextval
---------
       1
(1 row)

postgres=# select nextval('test_sequence_01');
 nextval
---------
       2
(1 row)

postgres=# select nextval('test_sequence_01');
 nextval
---------
       3
(1 row)

postgres=# select currval('test_sequence_01');
 currval
---------
       3
(1 row)

 

pg 시퀀스 정리 끝!😀