Using Sequences for IDs: Mastering PostgreSQL Generators

Previously in this course, we discussed the mechanics of data integrity in Understanding Primary Keys: Ensuring Data Integrity in PostgreSQL. While we often rely on SERIAL or IDENTITY columns to handle IDs automatically, understanding the underlying engine provides the control needed for complex migrations or manual data correction.

In this lesson, we look under the hood at PostgreSQL Sequences.

What is a Sequence?

A sequence is a special kind of database object that generates a rising series of numbers. Unlike a standard table column, a sequence exists independently. It doesn't care about your transactions or your table rows; it simply keeps a counter in memory and increments it whenever you ask.

Think of it as a digital "take-a-number" dispenser at a deli counter. When you pull a tab, the machine updates its internal state to the next number. If you skip a number or reset the machine, the next person just gets whatever is next in the sequence.

Creating and Using a Sequence

To see this in action, we’ll create a standalone sequence. This is useful when you need to share a single ID stream across multiple tables or handle legacy data imports.

SQL
-- Create a simple sequence starting at 1000
CREATE SEQUENCE order_id_seq START 1000;

-- Get the next value
SELECT nextval('order_id_seq');
-- Returns: 1000

-- Get the next value again
SELECT nextval('order_id_seq');
-- Returns: 1001

The nextval() function is the primary way we interact with these objects. It atomically retrieves the current value and increments the counter, ensuring that no two sessions ever receive the same ID simultaneously.

Linking Sequences to Columns

In our store application, we often use the IDENTITY property, which creates a sequence for us behind the scenes. However, you can explicitly link a manual sequence to a column using the DEFAULT clause.

Let’s advance our store project by creating a custom tracking system for "Special Requests" that uses a custom sequence:

SQL
-- Create a sequence for our special requests
CREATE SEQUENCE special_request_id_seq;

-- Apply to a table
CREATE TABLE special_requests (
    request_id INT DEFAULT nextval('special_request_id_seq') PRIMARY KEY,
    customer_id INT,
    request_details TEXT
);

By explicitly declaring DEFAULT nextval('special_request_id_seq'), you maintain full control over the sequence configuration. If you ever need to change the increment step—for instance, to increment by 5 instead of 1—you can simply run ALTER SEQUENCE special_request_id_seq INCREMENT BY 5;.

Manually Incrementing and Resetting IDs

Sometimes, you need to "jump" ahead or reset a sequence if a data import went wrong. You can manipulate the sequence state directly without affecting your actual table data:

SQL
-- See the current state of the sequence
SELECT last_value FROM special_request_id_seq;

-- Manually set the sequence to a specific number
SELECT setval('special_request_id_seq', 5000);

Caution: setval changes the generator's state. If you set it to a number already present in your table's request_id column, subsequent INSERT statements will fail with a "duplicate key" error. Always check your max ID before resetting a sequence.

Comparison: Serial vs. Identity vs. Manual

Hands-on Exercise

For this exercise, navigate to your store database and perform the following:

1. Create a new sequence named log_id_seq starting at 100.
2. Create a table named activity_logs with an id column that uses log_id_seq as its default.
3. Insert two rows into activity_logs.
4. Manually increment the sequence to 500 using setval.
5. Insert a third row and verify that its ID is 501.

Common Pitfalls

• Sequence Gaps: Sequences are not transactional in a way that prevents gaps. If a transaction fails after calling nextval(), that number is "burned" and cannot be recovered. This is normal behavior—don't treat sequence IDs as a strictly continuous audit trail.
• Permissions: If you have multiple database users, ensure the user inserting into the table has USAGE permissions on the sequence, otherwise, the INSERT will throw a permission error.
• Mixing Methods: Avoid mixing manual INSERT statements that provide an explicit ID with DEFAULT column values, as this causes the sequence to fall out of sync with your existing data.

FAQ

Can multiple tables use the same sequence? Yes. You can point the DEFAULT value of multiple tables to the same sequence object. This is a common pattern for global identifiers across different record types.

Is it better to use UUIDs or Sequences? Sequences are faster and produce human-readable, sequential IDs, but they are predictable. If security is a concern, check out our guide on Working with UUIDs: A Guide to Secure Primary Keys in PostgreSQL for a more robust alternative.

Recap

Sequences are the engine behind ID generation in PostgreSQL. By mastering nextval and setval, you gain the ability to manage identifiers manually, allowing for flexible schema design and robust data management as your store application grows.

Up next: We will learn how to move large datasets in and out of your tables using the COPY command.