PostgreSQL Sequence

Summary: in this tutorial, you’ll learn how to use the PostgreSQL Sequence objects to generate unique integer values.

Getting Started with PostgreSQL Sequence #

In PostgreSQL, a sequence is a database object that generates unique integer values.

To create a new sequence, you use the CREATE SEQUENCE statement. Here’s the basic syntax:

CREATE SEQUENCE sequence_name
    [START WITH start_value]
    [INCREMENT BY increment_value]
    [MINVALUE min_value | NO MINVALUE]
    [MAXVALUE max_value | NO MAXVALUE]
    [CYCLE | NO CYCLE]
    [CACHE cache_value | NO CACHE]
    [OWNED BY table_name.column_name ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

sequence_name defines a sequence name, which must be unique within the database.
START WITH start_value specifies the starting value of the sequence – the start_value defaults to 1.
INCREMENT BY increment_value determines the value you want to increment the sequence. The increment_value default is 1.
MINVALUE min_value sets the minimum value of the sequence. Use NO MINVALUE to set the default to 1 for ascending and -1 for descending sequences.
MAXVALUE max_value sets the maximum value of the sequence. Use NO MAXVALUE to set the default value for max_value to the maximum positive integer for ascending and -1 for descending sequences.
CYCLE instructs the sequence should restart for the MINVALUE for ascending sequences or MAXVALUE for descending sequences when it reaches its limit. Use NO CYCLE to throw an error if the sequence value reaches the limit.
CACHE cache_value instructs PostgreSQL to preallocate and store a number of sequence numbers in the memory for faster access—the cache_value defaults to 1. Use NO CACHE if you want to turn off the cache.
OWNED BY table_name.column_name associates the sequence with a table column.

Sequence functions #

PostgreSQL provides some useful functions to work with sequences:

nextval('sequence_name') increments the sequence to its next value and returns that value.
currval('sequence_name') returns the value most recently obtained by the nextval() function for the sequence in the current session.
setval('sequence_name', value, is_called) manually sets the current value of a sequence.
lastval() returns the value that the nextval() function has recently generated in the current session.

Creating an ascending sequence example #

First, create a new ascending sequence (asc_seq) starting with one and incrementing with one:

CREATE SEQUENCE asc_seq
START WITH 1 
INCREMENT BY 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, advance the sequence asc_seq to the next value:

SELECT nextval('asc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 nextval
---------
       1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you call the nextval() function, it continues advancing the asc_seq to the next value:

SELECT nextval('asc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 nextval
---------
       2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, get the current value of the asc_seq sequence:

SELECT currval('asc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 currval
---------
       2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a descending sequence example #

First, create a new descending sequence (desc_seq) with a decrement of -1:

CREATE SEQUENCE desc_seq
INCREMENT BY -1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, advance the sequence to the next value:

SELECT nextval('desc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 nextval
---------
      -1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you call the nextval() function again, it will advance the desc_seq sequence to the next value:

SELECT nextval('desc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 nextval
---------
      -2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, get the current value of the desc_seq sequence:

SELECT currval('desc_seq');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 currval
---------
      -2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Associating a sequence with a table column #

First, create a new table to store the transfer order header:

CREATE TABLE transfer_order_headers (
  order_no INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  warehouse_id INT NOT NULL,
  movement_type VARCHAR(20) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new table transfer_order_items to store order items.

CREATE TABLE transfer_order_items (
  order_no INT,
  item_no INT,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  from_bin VARCHAR(20) NOT NULL,
  to_bin VARCHAR(20) NOT NULL,
  PRIMARY KEY (order_no, item_no),
  FOREIGN KEY (order_no) REFERENCES transfer_order_headers(order_no)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, create a new sequence associated with the item_no column of the transfer_order_items table:

CREATE SEQUENCE item_no_seq 
START 10 
INCREMENT 10 
MINVALUE 10 
OWNED BY transfer_order_items.item_no;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, set the default value for the item_no column using the value generated by the item_no_seq sequence:

ALTER TABLE transfer_order_items 
ALTER COLUMN item_no
SET DEFAULT nextval('item_no_seq');Code language: PHP (php)

Fifth, create a new transfer order:

INSERT INTO
  transfer_order_headers (warehouse_id, movement_type)
VALUES
  (1, 'Picking') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no |          created_at           | warehouse_id | movement_type
----------+-------------------------------+--------------+---------------
        1 | 2024-12-08 22:45:43.173269-05 |            1 | Picking

Finally, create three items for the transfer order that moves the product id 1 and 2 from bin A-01 to B-01:

INSERT INTO
  transfer_order_items (order_no, product_id, quantity, from_bin, to_bin)
VALUES
  (1, 1, 5, 'A-01', 'B-01'),
  (1, 2, 10, 'A-01', 'B-01') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no | item_no | product_id | quantity | from_bin | to_bin
----------+---------+------------+----------+----------+--------
        1 |      10 |          1 |        5 | A-01     | B-01
        1 |      20 |          2 |       10 | A-01     | B-01

The sequence item_no_seq automatically generates values for the item_no column.

Removing a sequence #

You use the DROP SEQUENCE statement to remove a sequence:

DROP SEQUENCE [IF EXISTS] sequence_name
[CASCADE | RESTRICT];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the sequence name to drop.
Second, use the CASCADE option to remove the sequence and its dependent objects, and in turn, the objects that depend on the dependent objects, and so on.

For example, the following statement drops the asc_seq from the database:

DROP SEQUENCE IF EXISTS asc_seq;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Listing all sequences #

To list all sequences in a database, you use the following query:

SELECT * FROM pg_class
WHERE relkind = 'S';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

Use a sequence to generate unique integers automatically based on a specification.
Use the CREATE SEQUENCE statement to create a new sequence.
Use the nextval function to advance the sequence to the next value.
Use the currval function to get the value that the nextval function has recently generated.
Use the setval function to set the current value for a sequence manually.
Use the OWNED BY clause to associate a sequence with a table column.
Use the DROP SEQUENCE statement to remove a sequence.

Quiz #

Was this tutorial helpful ?