Sling Academy
Home/PostgreSQL/PostgreSQL: Create a sequence, advance a sequence, get current value, set value

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

Last updated: January 06, 2024

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.

Next Article: PostgreSQL: Restart, rename a sequence, and use pgAdmin to alter a sequence

Previous Article: PostgreSQL: How to add/update foreign key in an existing table

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB