PostgreSQL | Using Sequences

Create a Sequence

CREATE SEQUENCE [sequence_name];

Rename a Sequence

ALTER SEQUENCE [old_sequence_name] rename to [new_sequence_name];

List Sequences

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

Drop a Sequence

-- Drop seq_foo.
DROP SEQUENCE seq_foo;

Reset a Sequence

drop table tbl_ttt_seq;
drop sequence seq_ttt;

Change the Current Sequence Value

SELECT setval('sequence_name', desired_value);

Check Sequences

SELECT * FROM pg_class where relkind = 'S';

Create a Table

CREATE TABLE tbl_ttt_seq
(
    seq int not null default nextval('seq_ttt')
,   a varchar(10)
);

Automatically drop the sequence when the table is dropped. If this is not set, the sequence remains even after the table is dropped.

ALTER SEQUENCE seq_ttt OWNED BY tbl_ttt_seq.seq;

Insert Data

insert into tbl_ttt_seq (a) values ('aaa');
insert into tbl_ttt_seq (a) values ('bbb');
insert into tbl_ttt_seq (a) values ('ccc');
insert into tbl_ttt_seq (a) values ('ddd');
insert into tbl_ttt_seq (a) values ('eee');

select * from tbl_ttt_seq;

Check the Current Sequence Value

select currval('seq_ttt'), nextval('seq_ttt');

Change a Sequence

select setval('seq_ttt', 55);

Check Sequence Dependencies

SELECT p.relname, a.adsrc FROM pg_class p
JOIN pg_attrdef a ON (p.relfilenode = a.adrelid)
WHERE a.adsrc ~ 'seq_ttt';

Drop a Sequence in Use

alter table tbl_ttt_seq
alter column seq set default null;

drop sequence seq_ttt;

Source: http://dbrang.tistory.com/784 [dBRang]