Contents / Previous / Next

Sequences

Sequences(also called sequence generators) are special single-row tables created with CREATE SEQUENCE.

A sequence object is often used to generate unique identifiers for rows of a table, e.g., ID-numbers for each customer.

The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

Creating Sequences

CREATE SEQUENCE will enter a new sequence number generator (owned by the current user) into the current database: CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ] TEMPORARY sequences are automatically dropped on session exit.
The CACHE cache option enables faster access.
CYCLE may be used to enable the sequence to wrap around.

Although you cannot update a sequence directly, you can examine the parameters and current state of a sequence using a query like:

SELECT * FROM seqname;


Sequence-Manipulation Functions

Function Action
nextval('name') Returns the next available sequence number, and updates the counter
currval('name') Returns the sequence number from the previous nextval() call
setval('name', newval) Sets the sequence number counter to the specified value

Example "sequence creation and sequence function usage":

CREATE SEQUENCE counter ; SELECT nextval('counter') ; nextval --------- 1 SELECT currval('counter') ; currval --------- 1 SELECT setval('counter', 10) ; setval -------- 10


Using Sequences to Number Rows

Configuring a sequence to uniquely number rows involves several steps:
  1. Create the sequence.
  2. Create the table, defining nextval() as the column default.
  3. During the INSERT, do not supply a value for the sequenced column, or use nextval().

Example:

CREATE SEQUENCE customer_seq; CREATE TABLE customer ( id INTEGER DEFAULT nextval('customer_seq'), name CHAR(30) ); INSERT INTO customer (name) VALUES ('Jim Big'); INSERT INTO customer (name) VALUES ('Jim Boy'); SELECT * FROM customer ; id | name ----+-------------------------------- 1 | Jim Big 2 | Jim Boy (2 rows)


Serial Column Type

An easier way to use sequences to number rows is to define a column of type SERIAL.
A sequence will be automatically created, and will be assigned as DEFAULT to the column. Example: CREATE TABLE customer ( id SERIAL, name CHAR(30) ); INSERT INTO customer (name) VALUES ('Jim Big'); INSERT INTO customer (name) VALUES ('Jim Boy'); SELECT * FROM customer ; id | name ----+-------------------------------- 1 | Jim Big 2 | Jim Boy (2 rows)