Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Sequences

Andrey Aksenov

A sequence is a schema object that generates a series of unique integer values, in either ascending or descending order. Sequences are commonly used to generate unique identifiers for table rows. When a column is declared with a serial type — such as SERIAL or BIGSERIAL — a sequence is automatically created and associated with that column to provide incrementing values.

Overview

Greengage DB provides the following commands to create, alter, and drop a sequence:

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • DROP SEQUENCE

The syntax of the CREATE SEQUENCE command is described below:

CREATE [ TEMP ] SEQUENCE <sequence_name>
    [ INCREMENT [BY] <value> ]
    [ MINVALUE <minvalue> | NO MINVALUE ]
    [ MAXVALUE <maxvalue> | NO MAXVALUE ]
    [ START [WITH] <start> ]
    [ CACHE <cache> ]
    [ [NO] CYCLE ]
    [ OWNED BY { <table>.<column> | NONE } ]

The following are descriptions of the clauses in the CREATE SEQUENCE command:

  • CREATE SEQUENCE <sequence_name>

    Creates a new sequence with the specified name. A sequence name can be schema-qualified (<schema_name>.<sequence_name>).

  • TEMP

    If specified, a sequence object is created only for the current session.

  • INCREMENT [BY]

    Specifies the increment added to the current sequence value to produce the next one. A positive value sets an ascending sequence, and a negative one specifies a descending sequence.

  • MINVALUE <minvalue>

    Specifies the minimum value that a sequence can generate.

  • MAXVALUE <maxvalue>

    Specifies the maximum value for a sequence.

  • START [WITH] <start>

    Specifies the value at which to start a sequence.

  • CACHE <cache>

    Specifies the number of sequence values to be retrieved and stored in memory for faster access.

  • [NO] CYCLE

    Enables a sequence to wrap around to its start when it reaches maxvalue (ascending) or minvalue (descending). See the Sequence wrap-around section below.

  • OWNED BY

    Associates a sequence with a table column. If this column or its table is dropped, the sequence is automatically dropped.

Once the sequence is created, you can use the following sequence manipulation functions:

  • nextval() advances the sequence object to its next value and returns that value. See the Get a new sequence value section below.

  • setval() sets the sequence object’s current value and, optionally, the is_called flag. See the Set the current sequence value section below.

NOTE

The PostgreSQL currval() and lastval() sequence functions are not supported in Greengage DB.

Create a sequence

Prerequisites

To execute commands described in the following sections, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Then, create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

The CREATE SEQUENCE command below creates a sequence with the given name and start value:

CREATE SEQUENCE customer_id_seq START 101;

The customer_id_seq sequence starts at 101 and increments by 1 by default, generating unique numeric values each time it is called.

Use sequences in DML commands

Get a new sequence value

This section shows how to use the nextval() function to generate unique identifiers for table rows. The function takes the name of a sequence as its argument.

Create a sample table:

CREATE TABLE customers
(
    customer_id BIGINT,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

To insert data, use the INSERT INTO command. To generate customer ID values, the sequence name is passed to the nextval() function:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Andrew Fuller', 'andrew@example.com'),
       (nextval('customer_id_seq'), 'Michael Suyama', 'michael@testmail.com'),
       (nextval('customer_id_seq'), 'Laura Callahan', 'laura@example.io'),
       (nextval('customer_id_seq'), 'Nancy Davolio', 'nancy@samplemail.com'),
       (nextval('customer_id_seq'), 'Steven Buchanan', 'steven@fastmail.net');

Use the SELECT command to verify the data added to the table:

SELECT *
FROM customers
ORDER BY customer_id;

The output shows that each customer has a unique ID, starting from 101:

 customer_id |      name       |        email
-------------+-----------------+----------------------
         101 | Laura Callahan  | laura@example.io
         102 | Nancy Davolio   | nancy@samplemail.com
         103 | Michael Suyama  | michael@testmail.com
         104 | Andrew Fuller   | andrew@example.com
         105 | Steven Buchanan | steven@fastmail.net
(5 rows)

nextval() and transaction rollbacks

A nextval() operation is never rolled back. Once a value is fetched, it is considered used — even if the transaction that called nextval() fails. As a result, failed transactions can leave gaps in the sequence of assigned values.

The following request attempts to insert a new customer record but then rolls back the transaction, undoing the INSERT operation:

BEGIN;
INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Margaret Peacock', 'margaret@example.com');
ROLLBACK;

Use SELECT nextval() to check the current value of the sequence:

SELECT nextval('customer_id_seq');

The returned value should be 107, indicating that the sequence is incremented even though the INSERT operation is rolled back:

 nextval
---------
     107

Set the current sequence value

You can use the setval() function to set the current sequence value. This function also has the optional is_called parameter. If this parameter is set to true (default), the subsequent nextval() call advances the sequence before returning a value. If is_called is false, nextval() returns the last value.

The following command sets the current value of the customer_id_seq sequence to 200:

SELECT setval('customer_id_seq', 200);

To insert a new row to a table, use the INSERT INTO command:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Margaret Peacock', 'margaret@example.com');

Then, select data from the customers table:

SELECT *
FROM customers
ORDER BY customer_id;

The result should show that customer_id for the new row is 201:

 customer_id |       name       |        email
-------------+------------------+----------------------
         101 | Laura Callahan   | laura@example.io
         102 | Nancy Davolio    | nancy@samplemail.com
         103 | Michael Suyama   | michael@testmail.com
         104 | Andrew Fuller    | andrew@example.com
         105 | Steven Buchanan  | steven@fastmail.net
         201 | Margaret Peacock | margaret@example.com

Sequence wrap-around

By default, a sequence does not wrap around. When it reaches its maximum or minimum value (either the data type limit or the <maxvalue>/<minvalue> parameter value), any call to nextval() results in an error. To allow the sequence to wrap around and restart from the value set by the START [WITH] clause, enable the CYCLE option.

Create a new sequence with MAXVALUE set to 5:

CREATE SEQUENCE customer_id_seq_small MAXVALUE 5;

Create a sample table:

CREATE TABLE customers_with_small_sequence
(
    customer_id BIGINT,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Insert five values into the table:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Andrew Fuller', 'andrew@example.com'),
       (nextval('customer_id_seq_small'), 'Michael Suyama', 'michael@testmail.com'),
       (nextval('customer_id_seq_small'), 'Laura Callahan', 'laura@example.io'),
       (nextval('customer_id_seq_small'), 'Nancy Davolio', 'nancy@samplemail.com'),
       (nextval('customer_id_seq_small'), 'Steven Buchanan', 'steven@fastmail.net');

Then, try to insert a new value:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Margaret Peacock', 'margaret@example.com');

The following error is thrown:

ERROR:  nextval: reached maximum value of sequence "customer_id_seq_small" (5)

To allow the sequence to wrap around and restart from its start value, enable the CYCLE option using the ALTER SEQUENCE command:

ALTER SEQUENCE customer_id_seq_small CYCLE;

The INSERT request should be executed without errors:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Margaret Peacock', 'margaret@example.com');

Select data from the customers_with_small_sequence table:

SELECT *
FROM customers_with_small_sequence
ORDER BY customer_id;

The result shows that there are two customers with ID equal to 1:

 customer_id |       name       |        email
-------------+------------------+----------------------
           1 | Margaret Peacock | margaret@example.com
           1 | Andrew Fuller    | andrew@example.com
           2 | Michael Suyama   | michael@testmail.com
           3 | Steven Buchanan  | steven@fastmail.net
           4 | Nancy Davolio    | nancy@samplemail.com
           5 | Laura Callahan   | laura@example.io
(6 rows)

Use sequences in a table definition

Default column value

You can reference a sequence directly in the CREATE TABLE command, for example:

CREATE TABLE customers_with_sequence
(
    customer_id BIGINT DEFAULT nextval('customer_id_seq'),
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

If you insert rows without specifying customer_id values, Greengage DB automatically gets the next value from the customer_id_seq sequence:

INSERT INTO customers_with_sequence (name, email)
VALUES ('Andrew Fuller', 'andrew@example.com'),
       ('Michael Suyama', 'michael@testmail.com'),
       ('Laura Callahan', 'laura@example.io'),
       ('Nancy Davolio', 'nancy@samplemail.com'),
       ('Steven Buchanan', 'steven@fastmail.net');

Select data from the table:

SELECT *
FROM customers_with_sequence
ORDER BY customer_id;

The result should look like this:

 customer_id |      name       |        email
-------------+-----------------+----------------------
         202 | Andrew Fuller   | andrew@example.com
         203 | Michael Suyama  | michael@testmail.com
         204 | Laura Callahan  | laura@example.io
         205 | Nancy Davolio   | nancy@samplemail.com
         206 | Steven Buchanan | steven@fastmail.net
(5 rows)

Serial types

Declaring a column of a serial type (SMALLSERIAL, SERIAL, or BIGSERIAL) implicitly creates a sequence counter for use in that table column. The generated sequence is named using the pattern <table_name>_<column_name>_seq, and it can be used in DML commands just like any regular sequence. Learn more about serial types from the corresponding section in the PostgreSQL documentation: Serial Types.

In the example below, the customer_id column has the SERIAL type:

CREATE TABLE customers_with_serial
(
    customer_id SERIAL,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

When you insert rows, Greengage DB automatically gets the next value from the implicitly created sequence:

INSERT INTO customers_with_serial (name, email)
VALUES ('Andrew Fuller', 'andrew@example.com'),
       ('Michael Suyama', 'michael@testmail.com'),
       ('Laura Callahan', 'laura@example.io'),
       ('Nancy Davolio', 'nancy@samplemail.com'),
       ('Steven Buchanan', 'steven@fastmail.net');

Select data from the table:

SELECT *
FROM customers_with_serial
ORDER BY customer_id;

The result should look like this:

 customer_id |      name       |        email
-------------+-----------------+----------------------
           1 | Andrew Fuller   | andrew@example.com
           2 | Michael Suyama  | michael@testmail.com
           3 | Laura Callahan  | laura@example.io
           4 | Nancy Davolio   | nancy@samplemail.com
           5 | Steven Buchanan | steven@fastmail.net

Alter sequences

The ALTER SEQUENCE command changes the attributes of an existing sequence.

To restart the customer_id_seq sequence from 1001, use the following command:

ALTER SEQUENCE customer_id_seq RESTART WITH 1001;

Associate a sequence with the specified table column:

ALTER SEQUENCE customer_id_seq OWNED BY customers.customer_id;

Insert new data to the customers table:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Robert King', 'robert@demo.org'),
       (nextval('customer_id_seq'), 'Janet Leverling', 'janet@businessmail.com');

Then, select data:

SELECT *
FROM customers
ORDER BY customer_id;

The result shows that the IDs of newly added customers start from 1001:

 customer_id |       name       |         email
-------------+------------------+------------------------
         101 | Laura Callahan   | laura@example.io
         102 | Nancy Davolio    | nancy@samplemail.com
         103 | Michael Suyama   | michael@testmail.com
         104 | Andrew Fuller    | andrew@example.com
         105 | Steven Buchanan  | steven@fastmail.net
         201 | Margaret Peacock | margaret@example.com
        1001 | Robert King      | robert@demo.org
        1002 | Janet Leverling  | janet@businessmail.com
(8 rows)

View information about sequences

This section shows how to get information about sequences.

  • To get information about the specified sequence, use the SELECT command:

    SELECT *
FROM customer_id_seq;

    The result includes sequence settings, such as its start value, minimum and maximum values:

      sequence_name  | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 customer_id_seq |       1002 |         101 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t

  • To get the list of all sequences, use the \ds psql meta-command:

    \ds

    The result looks as follows:

                              List of relations
 Schema |                 Name                  |   Type   |  Owner
--------+---------------------------------------+----------+---------
 public | customer_id_seq                       | sequence | gpadmin
 public | customer_id_seq_small                 | sequence | gpadmin
 public | customers_with_serial_customer_id_seq | sequence | gpadmin

    The result also includes the automatically generated customers_with_serial_customer_id_seq sequence for the customer_id column of the SERIAL type.

  • To retrieve information about sequences, use the information_schema.sequences view:

    SELECT sequence_schema,
       sequence_name,
       data_type,
       minimum_value,
       maximum_value,
       increment,
       cycle_option
FROM information_schema.sequences;

    The result looks as follows:

     sequence_schema |             sequence_name             | data_type | minimum_value |    maximum_value    | increment | cycle_option
-----------------+---------------------------------------+-----------+---------------+---------------------+-----------+--------------
 public          | customer_id_seq                       | bigint    | 1             | 9223372036854775807 | 1         | NO
 public          | customer_id_seq_small                 | bigint    | 1             | 5                   | 1         | YES
 public          | customers_with_serial_customer_id_seq | bigint    | 1             | 9223372036854775807 | 1         | NO

Drop sequences

To drop a sequence, use the DROP SEQUENCE command:

DROP SEQUENCE customer_id_seq_small;

To drop a sequence along with related objects, such as tables that use it, use the CASCADE option:

DROP SEQUENCE customer_id_seq CASCADE;
Previous
Partitioning
Next
Indexes