How to use the PostgreSQL CREATE SEQUENCE Statement [Examples]

In PostgreSQL, sequences are database objects that generate unique sequential numbers. This article will show you how to create and use them using the CREATE SEQUENCE statement, and provide code examples.

Sequences in PostgreSQL

Sequences are most often used to generate primary keys in tables, or other unique identifiers. Temporary sequences can also be used when generating values or building loops for SELECT queries, but shouldn’t be confused with the generate_series function, which offers more flexibility and can be better suited for generating sequential data for querying and insertion where the values do not need to be unique.

PostgreSQL Syntax for CREATE SEQUENCE

The CREATE SEQUENCE statement is used to create new sequences in a PostgreSQL database. The syntax is as follows:

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

This looks a bit confusing, but most of the clauses above are optional, as you’ll see in the examples.

Note that:

• TEMPORARY, TEMP and UNLOGGED are optional
  • TEMPORARY/TEMP sequences exist only for the current database session
  • UNLOGGED sequences are not crash-safe, and will be reset if the server crashes or is not safely shut down
• name is the name of the new sequence
• AS can be used to specify an optional data_type
• INCREMENT specifies the value of each step in the sequence, defaulting to 1
• MINVALUE and MAXVALUE are both optional, and specify the minimum and maximum values that the sequence can generate
• START specifies the starting value of the sequence, and must fall between MINVALUE and MAXVALUE
• OWNED BY statement can be added to associate the sequence with a table or column, so that if that table or column is dropped, so is the sequence

Creating Sequences using CREATE SEQUENCE

Below, an example sequence is created, that generates a unique student_id, starting at 101 and ending at 999:

CREATE SEQUENCE student_id_sequence
START 101
INCREMENT 1
MINVALUE 101
MAXVALUE 999;

As you can see, the syntax will be quite simple for most use-cases. If you wanted, you could associate the sequence with the students table it will be used with, so that when the table is dropped (deleted), the sequence isn’t left behind:

CREATE SEQUENCE student_id_sequence
START 101
INCREMENT 1
MINVALUE 101
MAXVALUE 999
OWNED BY students;

Using PostgreSQL Sequences

Once a sequence has been created it can be used when defining columns:

CREATE TABLE students (
student_id bigint DEFAULT nextval('student_id_sequence') PRIMARY KEY,
name varchar(50) NOT NULL,
phone_number varchar(50) NOT NULL
);

Above, a students table is created, which has a student_id column which takes its default value from the student_id_sequence. Note the use of the nextval() function to get the next value in the sequence – You can also use currval() to get the most recently assigned value from the sequence.

Updating a Sequence

Sequences cannot be updated, but you can view their status by running:

SELECT * FROM sequence_name;