PostgreSQL: Create a sequence, advance a sequence, get current value, set value

Overview

Sequences in PostgreSQL are special schemas that generate numeric values. This tutorial explores how to create, manage, and use sequences for auto-incrementing values.

Creating a Sequence

Starting with the basics, creating a sequence in PostgreSQL is done with the CREATE SEQUENCE command. This sets up a new sequence generator that can be used to create unique numbers, typically for use as primary keys.

CREATE SEQUENCE my_sequence;

By default, a sequence starts at 1 and advances by 1, but these values can be customized:

CREATE SEQUENCE my_sequence
  START WITH 100
  INCREMENT BY 10;

Advancing a Sequence

Every time you want to generate a new number (advance the sequence), you call NEXTVAL:

SELECT NEXTVAL('my_sequence');

This increments the sequence by its set value and returns the new number.

Getting the Current Value

To get the current value of the sequence without advancing it, you use the CURRVAL function:

SELECT CURRVAL('my_sequence');

It is important to remember that CURRVAL can only be called after NEXTVAL has been called at least once in the current session.

Setting a Sequence’s Value

If you need to change the current value of the sequence at any point, you use the SETVAL command. This can set the sequence to any specific value:

SELECT SETVAL('my_sequence', 200);

It’s also possible to set the value and advance the sequence in the same command:

SELECT SETVAL('my_sequence', 200, TRUE);

In the advanced use cases, you may want to tie a sequence to a specific column or table, or even reset the sequence based on the maximal value from a table:

Associating a Sequence with a Table

Sequences are often used with a table’s ID column to create auto-incrementing primary keys:

CREATE TABLE my_table (
  id INTEGER NOT NULL DEFAULT NEXTVAL('my_sequence'),
  payload TEXT
);

Resetting a Sequence

If you need to reset a sequence based on the rows of a table—for instance, after importing data—you may use:

SELECT SETVAL(
  'my_sequence',
  (SELECT MAX(id) FROM my_table)
);

This ensures the sequence starts after the highest current ID.

Performance Considerations

Sequences are designed to be safe for concurrent use. This means multiple sessions can concurrently request NEXTVAL without fear of receiving duplicate values.

While sequences are handy, there are some performance overheads when they’re heavily used, particularly in high-throughput environments. It’s essential to balance the use of sequences with application requirements.

Conclusion

In summary, sequences are powerful tools in PostgreSQL that facilitate the generation of unique identifiers. Through commands like CREATE SEQUENCE, NEXTVAL, CURRVAL, and SETVAL, developers can effectively manage numeric sequences for various use cases within a database.