How to create an auto-increment sequence in PostgreSQL?

1 year ago
Ava Mitchell
1 minute

To create an auto-increment sequence in PostgreSQL, you can use the following syntax:

CREATE SEQUENCE sequence_name
    [INCREMENT BY increment]
    [MINVALUE minvalue]
    [MAXVALUE maxvalue]
    [START WITH start]
    [CACHE cache]
    [CYCLE | NO CYCLE]
    [OWNED BY table_name.column_name];

sequence_name is the name of the sequence you want to create, increment is the value by which the sequence will be incremented each time, defaulting to 1. minvalue and maxvalue are the minimum and maximum values of the sequence, start is the starting value of the sequence, defaulting to 1. cache is the number of sequence values that are cached, defaulting to 1. CYCLE indicates that the sequence can cyclically repeat, while NO CYCLE indicates that the sequence will not repeat. table_name.column_name refers to the table and column that own the sequence.

Here is an example:

CREATE SEQUENCE employee_id_seq
    INCREMENT BY 1
    START WITH 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

This will create a sequence named employee_id_seq that increments by 1 each time, starting at 1, with no minimum or maximum values, and caching one sequence value at a time.

bannerAds