반응형
/********************************************************************************************
-- Title : [PGS9.2] Handle Sequence - ver.dBRang
-- Reference : dbrang.tistory.com
-- Key word : sequence 시퀀스
********************************************************************************************/
-- Title : [PGS9.2] Handle Sequence - ver.dBRang
-- Reference : dbrang.tistory.com
-- Key word : sequence 시퀀스
********************************************************************************************/
-- Initiate
drop table ttt;
drop sequence ttt_a_seq;
-- Create Sequence
create sequence ttt_a_seq
start with 1
increment by 1
no minvalue
no maxvalue
cache 1;
-- Create Table -----------------------------------------------
create table ttt
( a int default nextval('ttt_a_seq'::regclass) not null /* need DEFAULT */
, b varchar(10)
);
-- Query System Catalog
select relname, relkind
from pg_class
where relname like '%ttt%'
and relkind = 'S';
relname | relkind
-----------+---------
ttt_a_seq | S
-- Test currval & nextval
select currval('ttt_a_seq') "CURRVAL"; /* if no data, return error! */
select nextval('ttt_a_seq') "NEXTVAL"; /* +1 */
select currval('ttt_a_seq') "CURRVAL"; /* 1 */
insert into ttt (b)
values ('aaaaa'), ('bbbbb');
select * from ttt;
a | b
---+-------
2 | aaaaa
3 | bbbbb
select currval('ttt_a_seq') "CURRVAL";
-- True/False option of pg_catalog.setval
SELECT pg_catalog.setval('ttt_a_seq', 200, true);
select currval('ttt_a_seq') "CURRVAL"; /* 200 */
insert into ttt (b)
values ('ddddd'), ('eeeee');
select * from ttt;
a | b
-----+-------
2 | aaaaa
3 | bbbbb
201 | ddddd /* 201부터 저장 */
202 | eeeee
SELECT pg_catalog.setval('ttt_a_seq', 400, false);
select currval('ttt_a_seq') "CURRVAL"; /* 202 */
insert into ttt (b)
values ('fffff'), ('ggggg');
select * from ttt;
a | b
-----+-------
2 | aaaaa
3 | bbbbb
201 | ddddd
202 | eeeee
400 | fffff /* 400부터 저장 */
401 | ggggg
-- Test alter sequence
alter sequence ttt_a_seq restart with 601;
select currval('ttt_a_seq') "CURRVAL"; /* 401 */
insert into ttt (b)
values ('hhhhh'), ('iiiii');
select * from ttt;
a | b
-----+-------
2 | aaaaa
3 | bbbbb
201 | ddddd
202 | eeeee
400 | fffff
401 | ggggg
601 | hhhhh
602 | iiiii
alter sequence ttt_a_seq
increment by 2
restart with 1000;
select currval('ttt_a_seq') "CURRVAL"; /* 602 */
insert into ttt (b)
values ('qqqqq'), ('wwwww');
select * from ttt;
a | b
-----+-------
2 | aaaaa
3 | bbbbb
201 | ddddd
202 | eeeee
400 | fffff
401 | ggggg
601 | hhhhh
602 | iiiii
select * from ttt;
a | b
------+-------
2 | aaaaa
3 | bbbbb
201 | ddddd
202 | eeeee
400 | fffff
401 | ggggg
601 | hhhhh
602 | iiiii
1000 | qqqqq
1002 | wwwww
반응형