SQLANDPLSQL.COM

What is a Sequence ?

Sequence is a database object which used to generate unique identifiers for rows of a table. In general Sequence maintains the uniqueness of the row within the table

Example :-

create sequence sample_seq start with 1 increment by 1;

create sequence sample_seq1 start with 1 increment by 1 maxvalue  1000;

If you create a table with ‘SERIAL’ data type, automatically sequence will be created, see below

postgres=# create table sample(id serial, name text);
CREATE TABLE

postgres=# \d sample_id_seq
                   Sequence "public.sample_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.sample.id

NEXTVAL

nextval will generate next values for the given sequence

postgres=# select nextval('sample_id_seq');
 nextval 
---------
       1
(1 row)

postgres=# select nextval('sample_id_seq');
 nextval 
---------
       2
(1 row)

CURRVAL

currval returns current value of the seqeunce

postgres=# select currval('sample_id_seq');
 currval 
---------
       2
(1 row)

SETVAL

setval sets the value of a sequence

postgres=# select currval('sample_id_seq');
 currval 
---------
       2
(1 row)

postgres=# select setval('sample_id_seq',5);
 setval 
--------
      5
(1 row)

postgres=# select setval('sample_id_seq',5, true); -- same as above query
 setval 
--------
      5
(1 row)

postgres=# select setval('sample_id_seq',5, false); 
-- next nextval query will return 5
 setval 
--------
      5
(1 row)

LASTVAL

lastval returns the last value of the sequence in the session. It does not take the sequence name as argument

postgres=# select lastval();
 lastval 
---------
     5
(1 row)


postgres=# select currval('sample_id_seq');
 currval 
---------
       5
(1 row)

postgres=# select setval('sample_id_seq',10); -- jump to 10
 setval 
--------
     10
(1 row)

postgres=# select nextval('sample_id_seq'); -- jump to 11
 nextval 
---------
      11
(1 row)

postgres=# select lastval();
 lastval 
---------
      11
(1 row)