exclusive content published on January 2, 2007

Sequences in the open source PostgreSQL database are special tables used for generating integer sequences. Typically, they are used to create a unique record ID (or key) for each row in a table. Read on to dig a little deeper into sequences and discover their full potential.

Why use a sequence?

Data in a relational tables is usually retrieved by referencing a key or index. Sometimes tables are built with multiple indexes to provide several paths to the data. Frequently, it is convenient to have a unique record ID that is unrelated to the data to easily manipulate records after they are retrieved. As a general practice, I add a unique ID field at the start of all tables. For performance and consistency, it is useful to have the database itself generate the unique ID values and that is the purpose of sequences.

Behind the scenes, PostgreSQL already creates a globally unique Object Identifier (OID) for each record in every table. The OID can be referenced in SQL statements, but cannot be changed by a user. Also, OIDs are not backed up by default and will change after a backup/restore unless special precautions are taken. Instead of working with the limitations of the OID, sequences are easier and more flexible to use for record IDs.

For comparison, the Oracle database uses sequences in a similar manner to PostgreSQL, while MySQL uses the "AUTO_INCREMENT" data type with the details mostly hidden from the user.

Creating and destroying sequences

You can create a sequence directly using SQL:
CREATE SEQUENCE myseq;

Or more often, indirectly, using the "SERIAL" data type:

CREATE TABLE seqtest (
  id SERIAL
  data TEXT
);

When the SERIAL data type is used, PostgreSQL automatically creates a sequence named tablename_fieldname_seq or in the example above, seqtest_id_seq.

You can destroy a sequence directly using SQL:
DROP SEQUENCE myseq;

Or more often, indirectly, by dropping the table that uses the sequence.

Changing an existing sequence

From the PostgreSQL command monitor, psql, a sequence looks like any other table. It can be queried using SQL SELECT statements, the structure and permission can be viewed using psql commands, but it can't be modified using SQL INSERT or UPDATE statements. Instead, there are three special functions used to retrieve or set sequence values.

NEXTVAL('sequence') returns the next available sequence number
CURRVAL('sequence') returns the value of the previous nextval()
SETVAL('sequence', value) sets the sequence to the specified value

Here is an example using the sequence we created earlier:
SELECT NEXTVAL('myseq')

If a record is inserted into a table with a sequence and NULL is used for the sequence field (simply do not include a value with the INSERT), PostgreSQL will automatically insert the NEXTVAL() for the sequence. This is the typical way sequences are used.

Another way to change a sequence is to use SQL. The ALTER SEQUENCE command can change a sequence using the same syntax as the CREATE SEQUENCE command. For example, to change the minimum value to 100, use:
ALTER SEQUENCE myseq MINVALUE 100;

Inside the numbers

PostgreSQL sequences all have the following structure:

    Column     |  Type
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean

Notice the values are based on bigint arithmetic, so the range cannot exceed the range of a 64-bit signed integer (-9223372036854775808 to 9223372036854775807).

Following is a definition of each column and the default value (if any):

sequence_name

the name of the sequence

last_value

the last value generated by the sequence

increment_by

the amount the value is incremented (or decremented), the default is 1. Negative numbers are allowed.

max_value

the maximum value of the sequence, default is 9223372036854775807, absolute maximum is 9223372036854775807.

min_value

the minimum value of the sequence, default is 1, absolute minimum value is -9223372036854775808.

cache_value

tells PostgreSQL to pre-allocate a number of sequences in memory to improve performance (usually for INSERT heavy programs), the default value is 1 (cache one value).

log_cnt

internal use, to prevent logging each fetched value, pre-log a few fetches in advance, the default is 32.

is_cycled

allows the sequence to wrap around when minvalue or maxvalue is reached, the default is false.

is_called

internal use, used by SETVAL/NEXTVAL to return the current value or next value after a SETVAL operation, typically used in pg_dump/pg_restore, the default is true.

Can two tables share a sequence?

Yes, multiple tables can share a sequence. The second and subsequent tables must explicitly reference an existing sequence instead of using the SERIAL data type. Using SERIAL creates a new sequence. A sequence used by more than one table will not be dropped if one of the tables is dropped. The sequence is dropped only when the last table using the sequence is dropped.

The final count down

While sequences that increase are common, the increment value of a sequence can also be negative. In that case, make sure the last_value in the sequence is set where you want to start generating numbers. Then, it will count down from the last_value to the min_value instead of up to the max_value.

This work is licensed under a Creative Commons Attribution-NonCommercial 2.5 License.