[PGS9.2] Handle Sequence - ver.dBRang

/********************************************************************************************
-- 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