sequence vs serial (postgresql의 auto increment)

table의 PK로 인덱스와 같이 증가하는 정수형 값을 사용하는 경우가 많습니다.
이를 위해 mysql에선 AUTO INCREMENT를 사용하였는데, postgresql에선 지원하지 않아,
해당 기능을 사용할 수 있는 문법들을 정리해보겠습니다.

 

sequence

예시 코드는 다음과 같습니다.

create sequence test_seq;

select nextval('test_seq'); -- 2
select curval('test_seq'); -- 1

create table contact(
    cont_id     integer	not null default nextval('test_seq'),
    cont_name	varchar(20),
    cont_tel	char(11)
);

alter sequence test_seq owned by contact.cont_id;

자동 증가하는 기능의 sequence를 만들고

이를 PK로 쓰일 컬럼과 연결하고

해당 PK를 위해 만들어진 sequence이기 때문에 소유 또한 바꿔줍니다.

(alter ~~,  이 경우 contact 테이블을 삭제하면 sequence도 함께 삭제됨)

 

serial

sequence의 경우 테이블 생성 이전에 sequence를 만들어야 하고, 권한도 옮겨야 하며, 테이블 생성 시 코드가 길고 문법이 복잡한 단점이 있습니다. 이를 위해 postgresql에선 serial이란 자료형을 제공합니다.

serial은 내부적으로 sequence를 사용했을 때 코드로 변환되어, sequence와 동일하게 동작합니다.

예제 코드는 다음과 같습니다.

create table contact(
    cont_id     serial	primary key,
    cont_name 	varchar(20),
    cont_tel 	char(11)
);

데이터 범위는 기본적으로 Integer이고, 더 작은 범위나 큰 범위를 사용하고 싶을 땐 smallserial, bigserial로 사용하면 됩니다.

 

generated always as identity

serial의 경우 postgres 자체 기능이기 때문에 추후 migration 등을 할 때 문제가 생길 수 있습니다.

때문에 postgres 10 부터는 ANSI SQL 2003 규약에 맞춰 generated 기능을 지원합니다.

 

generated {always | by default} as identity

always : insert시,해당 칼럼에 직접 값을 넣을 경우 오류 발생

by default : insert시,해당 칼럼에 직접 값을 넣을 경우 오류 발생 안 함

 

예제 코드

create table contact(
	cont_id	integer generated always as identity,
    cont_name varchar(20),
    cont_tel char(11)
);

create table contact2(
	cont_id	integer generated by default as identity,
    cont_name varchar(20),
    cont_tel char(11)
);