[PostgreSQL] 자동 증가(Auto Increment) 컬럼 사용법

PostgreSQL에서 값이 자동으로 증가하는 컬럼을 만들어 사용하는 방법은 아래 3가지

- 시퀀스 사용
- SERIAL 컬럼 사용
- GENERATED AS IDENTITY 컬럼 사용

여러 특징을 고려했을 때 generated always as identity가 자동증가 컬럼을 사용하는 이유에 가장 적합해 보입니다.
여기서 시퀀스 사용은 오래전부터 많이 사용해 오는 방식이므로 설명하지 않습니다.

 
 

1. SERIAL 컬럼

자동으로 시퀀스를 생성하여 채번에 사용함
serial 컬럼은 integer 컬럼 타입으로 생성됨
해당 컬럼에 직접 데이터 입력 가능(시퀀스 번호가 같이 증가하지 않음 주의)

create table seri_t1 (c1 serial, c2 varchar(30)) ;

 
 

2. IDENTITY 컬럼

serial 컬럼과 동일하게 내부적으로 시퀀스를 만들어 채번에 사용

1) GENERATED ALWAYS

해당 컬럼에 직접 데이터 입력 불가능(override system value 구문 사용한 강제 입력은 가능)

create table gen_t1 (c1 integer generated always as identity, c2 varchar(30)) ;

2) GENERATED BY DEFAULT

serial 컬럼과 동일하게 작동하며 해당 컬럼에 직접 데이터 입력 가능(순번이 자동으로 증가하지 않으므로 주의)

create table gendef_t1 (c1 integer generated by default as identity, c2 varchar(30)) ;

 

반응형

3. 자동 증가 컬럼 사용 테스트

1) 자동 증가 컬럼 테이블 생성

serial과 identity 컬럼의 column_default 값 차이 있음
always/by default 옵션별 identity_generation 컬럼 값 차이 있음
자동 증가 컬럼에 해당하는 시퀀스 자동 생성됨

-- 테이블 생성
create table seri_t1 (c1 serial, c2 varchar(30)) ;
create table gen_t1 (c1 integer generated always as identity, c2 varchar(30)) ;
create table gendef_t1 (c1 integer generated by default as identity, c2 varchar(30)) ;
 
-- 테이블 컬럼 속성 조회
select
table_name, column_name, ordinal_position, column_default, is_nullable, data_type, is_identity, identity_generation
from information_schema.columns
where table_name in ('seri_t1', 'gen_t1', 'gendef_t1')
order by table_name, ordinal_position
;
 table_name | column_name | ordinal_position |           column_default            | is_nullable |     data_type     | is_identity | identity_generation
------------+-------------+------------------+-------------------------------------+-------------+-------------------+-------------+---------------------
 gen_t1     | c1          |                1 |                                     | NO          | integer           | YES         | ALWAYS
 gen_t1     | c2          |                2 |                                     | YES         | character varying | NO          |
 gendef_t1  | c1          |                1 |                                     | NO          | integer           | YES         | BY DEFAULT
 gendef_t1  | c2          |                2 |                                     | YES         | character varying | NO          |
 seri_t1    | c1          |                1 | nextval('seri_t1_c1_seq'::regclass) | NO          | integer           | NO          |
 seri_t1    | c2          |                2 |                                     | YES         | character varying | NO          |
(6 rows)
 
-- 테이블 컬럼에 해당하는 시퀀스 생성되어있음
select * from pg_catalog.pg_sequences;
 schemaname |   sequencename   | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+------------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq   | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          
 public     | gen_t1_c1_seq    | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          
 public     | gendef_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          
(3 rows)

2) serial 테이블 INSERT

serial 컬럼 빼고 insert시 자동으로 데이터 입력됨
serial 컬럼 직접 입력 가능
직접 입력 시 해당 컬럼에서 사용하는 시퀀스 번호는 같이 변하지 않으므로 주의(PK등 제약 조건 있을 시 에러 발생)

-- serial 컬럼 빼고 insert
insert into seri_t1(c2) values ('a') ;

select * from seri_t1 ;
 c1 | c2
----+----
  1 | a
(1 row)
 
-- 시퀀스 last_value 값 증가함
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          1
 gen_t1_c1_seq    |          
 gendef_t1_c1_seq |          
(3 rows)
 
-- serial 컬럼 포함 insert
insert into seri_t1 values (2,'b') ;
INSERT 0 1
 
select * from seri_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
(2 rows)
 
-- 시퀀스 last_value 변하지 않음
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          1
 gen_t1_c1_seq    |          
 gendef_t1_c1_seq |          
(3 rows)
 
-- serial 컬럼 빼고 insert
insert into seri_t1(c2) values ('c') ;
INSERT 0 1
 
-- 시퀀스 NEXTVAL 2가 들어가 직적 입력한 2와 중복
select * from seri_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
  2 | c
(3 rows)

 
serial 컬럼 PK이고 값 직접 입력 시 중복 에러 발생 할 수 있음

-- 테이블 조회
select * from seri_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
(2 rows)
 
-- 시퀀스 last_value = 1로 신규 insert시 2가 채번되어 입력될 것
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          1
(3 rows)
 
-- C1이 PK였으면 에러 발생함
insert into seri_t1(c2) values ('c') ;
오류:  중복된 키 값이 "seri_t1_pkey" 고유 제약 조건을 위반함
DETAIL:  (c1)=(2) 키가 이미 있습니다.
 
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          
 gendef_t1_c1_seq |          
(3 rows)

3) generated always 테이블 insert 

자동 증가 컬럼 직접 입력 시 에러 발생

-- 자동 증가 컬럼 제외 inser시 자동으로 데이터 입력됨
insert into gen_t1(c2) values ('a') ;
INSERT 0 1
 
select * from gen_t1 ;
 c1 | c2
----+----
  1 | a
(1 row)
 
-- 해당 시퀀스 값 변경됨
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          1
 gendef_t1_c1_seq |          
(3 rows)
 
-- 자동 증가 컬럼 직접 입력(에러발생)
insert into gen_t1 values (2,'b') ;
오류:  cannot insert a non-DEFAULT value into column "c1"
DETAIL:  Column "c1" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
 
select * from gen_t1 ;
 c1 | c2
----+----
  1 | a
(1 row)
 
-- 시퀀스 값은 변경 없음
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          1
 gendef_t1_c1_seq |          
(3 rows)
 
insert into gen_t1(c2) values ('c') ;
INSERT 0 1

select * from gen_t1 ;
 c1 | c2
----+----
  1 | a
  2 | c
(2 rows)
 
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          2
 gendef_t1_c1_seq |          
(3 rows)

 
강제로 직접 입력 가능 하지만 시퀀스 값은 변하지 않으므로 주의

select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          2
 gendef_t1_c1_seq |          
(3 rows)
 
-- 자동증가 컬럼 값 강제입력
insert into gen_t1 overriding system value values (3,'b') ;
INSERT 0 1
psk1=# select * from gen_t1 ;
 c1 | c2
----+----
  1 | a
  2 | c
  3 | b
(3 rows)
 
-- 시퀀스 값은 변하지 않아 중복 값 insert
insert into gen_t1(c2) values ('d') ;
INSERT 0 1
 
select * from gen_t1 ;
 c1 | c2
----+----
  1 | a
  2 | c
  3 | b
  3 | d
(4 rows)

4) generated by default 테이블 insert

serial 컬럼과 동일하게 작동한다

-- 자동 증가 컬럼 빼고 insert
insert into gendef_t1(c2) values ('a') ;
INSERT 0 1

select * from gendef_t1 ;
 c1 | c2
----+----
  1 | a
(1 row)

-- 시퀀스 번호 증가함
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          2
 gendef_t1_c1_seq |          1
(3 rows)
 
-- 직접 입력 가능 
insert into gendef_t1 values (2,'b') ;
INSERT 0 1

select * from gendef_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
(2 rows)

-- 시퀀스 번호는 변하지 않음
select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          2
 gendef_t1_c1_seq |          1
(3 rows)
 
-- 중복 데이터 insert 
insert into gendef_t1(c2) values ('c') ;
INSERT 0 1

select * from gendef_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
  2 | c
(3 rows)

select sequencename , last_value from pg_catalog.pg_sequences ;
   sequencename   | last_value
------------------+------------
 seri_t1_c1_seq   |          2
 gen_t1_c1_seq    |          2
 gendef_t1_c1_seq |          2
(3 rows)

 
 

4. 시퀀스 값 변경 방법

• ALTER SEQUENCE

DDL로 작업 완료 시 까지 다른 세션에서 해당 시퀀스 조회 불가

-- 신규 채번 시 3부터 시작
alter sequence seri_t1_c1_seq restart with 3 ;
ALTER SEQUENCE
 
-- last_value 컬럼 값이 없어짐
select * from pg_catalog.pg_sequences where sequencename ='seri_t1_c1_seq' ;
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          
(1 row)
 
insert into seri_t1(c2) values ('c') ;
INSERT 0 1

select * from seri_t1 ;
 c1 | c2
----+----
  1 | a
  2 | b
  3 | c
(3 rows)
 
select * from pg_catalog.pg_sequences where sequencename ='seri_t1_c1_seq' ;
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          3
(1 row)

 

• setval

작업 중 다른 세션에서 채번 가능하므로 변경 값을 여유 있게 주는 게 좋음

select * from pg_catalog.pg_sequences where sequencename ='seri_t1_c1_seq' ;
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |         12
(1 row)
 
-- 100 부터 시작하도록 변경
select setval('seri_t1_c1_seq', 100, false);
 setval
--------
    100
(1 row)
 
select * from pg_catalog.pg_sequences where sequencename ='seri_t1_c1_seq' ;
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |          
(1 row)
 
-- 시퀀스 채번 시 100 채번됨
select nextval('seri_t1_c1_seq');
 nextval
---------
     100
(1 row)
 
select * from pg_catalog.pg_sequences where sequencename ='seri_t1_c1_seq' ;
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | seri_t1_c1_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |        100
(1 row)