SoFunction
Updated on 2025-04-11

Detailed explanation of how to use Sequence in PostgreSQL database

1. Create a Sequence

You can useCREATE SEQUENCEStatement to create a sequence.

CREATE SEQUENCE my_sequence;

By default, the sequence starts at 1 and increments by 1 each time. You can specify other options such as start value, minimum value, maximum value, increment step size, etc.

CREATE SEQUENCE my_sequence
    START 100
    INCREMENT 10
    MINVALUE 50
    MAXVALUE 500
    CYCLE;
  • START: The starting value of the sequence.
  • INCREMENT: Each callnextvalvalue increased when .
  • MINVALUE: The minimum value of the sequence.
  • MAXVALUE: The maximum value of the sequence.
  • CYCLE: When the maximum value is reached, the sequence starts again from the minimum value.

2. Use nextval to get the next value

You can usenextval()Function to get the next value of the sequence.

SELECT nextval('my_sequence');

Each callnextval(), the values ​​of the sequence will be incremented and a new value will be returned.

3. Use currval to get the current value

You can usecurrval()Function to get the current value of the sequence in the current session.

SELECT currval('my_sequence');

Note: In the current session, it must be called at least once.nextval()back,currval()Only then can return the value.

4. Use setval to set the value of the sequence

You can usesetval()Function to manually set the current value of the sequence.

SELECT setval('my_sequence', 500);

You can also choose whether to increment the sequence after setting:

SELECT setval('my_sequence', 500, false);
  • The second parameter is the new value.
  • The third parameter is a boolean value.trueIndicates the next callnextval()Will increment on this value,falseIndicates the next callnextval()This value will be returned directly.

5. Delete Sequence

If you no longer need a sequence, you can useDROP SEQUENCEstatement to delete it.

DROP SEQUENCE my_sequence;

6. Use Sequence in the table

Typically, sequences are used in conjunction with columns of a table (such as autoincrement ID). You can associate a sequence with a column when creating a table.

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,  -- SERIAL Yes to use Sequence Easy way
    name VARCHAR(50)
);

SERIALData types are actually a simple way to write PostgreSQL, which automatically creates a sequence and binds it to that column.

You can also manually specify a sequence:

CREATE SEQUENCE my_sequence;

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY DEFAULT nextval('my_sequence'),
    name VARCHAR(50)
);

7. View existing Sequence

You can query the system tablepg_sequencesTo view all sequences in the database.

SELECT * FROM pg_sequences WHERE sequencename = 'my_sequence';

8. Reset the sequence

Sometimes you may need to reset the value of the sequence, for example when the data in the table is deleted and reinserted, you may want the sequence to start over from a certain value.

SELECT setval('my_table_id_seq', 1);

Summarize

  • CREATE SEQUENCEUsed to create sequences.
  • nextval()Used to get the next value of the sequence.
  • currval()Used to get the current value of the sequence.
  • setval()Used to set the value of the sequence.
  • DROP SEQUENCEUsed to delete sequences.
  • SERIALis an easy way PostgreSQL provides to automatically create and manage sequences.

With these operations, you can use sequences flexibly in PostgreSQL to generate and manage unique identifiers.

This is the end of this article about the detailed explanation of how to use Sequence in PostgreSQL database. For more information about the usage of PostgreSQL Sequence, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!