Month: May 2019

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions:

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence:

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

Name	Type	References	Description
schemaname	name	pg_namespace.nspname	Name of schema containing sequence
sequencename	name	pg_class.relname	Name of sequence
sequenceowner	name	pg_authid.rolname	Name of sequence’s owner
data_type	regtype	pg_type.oid	Data type of the sequence
start_value	bigint		Start value of the sequence
min_value	bigint		Minimum value of the sequence
max_value	bigint		Maximum value of the sequence
increment_by	bigint		Increment value of the sequence
cycle	boolean		Whether the sequence cycles
cache_size	bigint		Cache size of the sequence
last_value	bigint		The last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns