SQL SEQUENCE in PostgreSQL: one ID generator for multiple tables

When you need to generate unique identifiers in PostgreSQL, the first thing that comes to mind is SERIAL. This mechanism is simple to use and handles the task perfectly in most cases. But what if you face a more complex challenge?

Imagine this situation: you need to create a document numbering system where numbers must be shared across multiple tables. For example, invoices, delivery notes, and acts should be numbered from a single numeric sequence: 100001, 100002, 100003... Using separate SERIAL for each table will lead to duplicate numbers, which is unacceptable 🚫

This is where sequences (SEQUENCE) come into play — a powerful tool for generating unique identifiers with flexibility that SERIAL cannot provide.

What is a Sequence?

A sequence is a special database object that generates an ordered series of unique numeric values according to a defined rule.

Think of an automatic document numbering machine in an office: every time a new document arrives, the machine issues the next number. A sequence works exactly the same way, but in a database.

Simple example: you have a counter set to 1000. When you ask for "the next number", it returns 1000 and increments to 1001. Next time it returns 1001, then 1002, and so on. Simple and reliable! ✨

Why use sequences?

Sequences provide much more control and flexibility than SERIAL. Here are their key advantages:

• 🔗 Independence from tables
  A single sequence can be used to generate IDs for multiple tables simultaneously. For example, invoices, delivery notes, and acts can be numbered from a single numeric series — this guarantees unique document numbers across the entire system.

• 🎨 Configuration flexibility
  You can start from any number (e.g., 1000), use countdown, configure cyclic numbering, or change the increment step.

Creating a Sequence

Sequences are created using the CREATE SEQUENCE command.

Basic Syntax

PostgreSQL 17.5
CREATE SEQUENCE sequence_name
    [START WITH initial_value]
    [INCREMENT BY increment_step]
    [MINVALUE minimum_value]
    [MAXVALUE maximum_value]
    [CYCLE | NO CYCLE]
    [CACHE number];

Sequence Parameters

For example, let's create a sequence for generating order numbers, starting from 1000:

PostgreSQL 17.5
CREATE SEQUENCE order_id_seq
    START WITH 1000
    INCREMENT BY 1
    NO CYCLE;

Functions for Working with Sequences

PostgreSQL provides several functions for working with sequences:

nextval() — get next value

PostgreSQL 17.5
SELECT nextval('order_id_seq');

This function increments the counter and returns the new value. ⬆️

When called again:

PostgreSQL 17.5
SELECT nextval('order_id_seq');

currval() — get current value

PostgreSQL 17.5
SELECT currval('order_id_seq');

Returns the last value obtained via nextval() in the current session, without changing the counter. 👀

⚠️ Important gotcha! When first using currval(), you might get an error "currval of sequence is not yet defined in this session". The reason: currval() can only be called after at least one nextval() call in the same session!

The logic is simple: you can't get the "current" value if it hasn't been requested yet in the current session. 🤔

setval() — set value

PostgreSQL 17.5
SELECT setval('order_id_seq', 5000);

Sets the current value of the sequence. The next nextval() call will return 5001. 🎯

Application in Tables

Method 1: Using DEFAULT

The most common way is to use the sequence as a default value:

PostgreSQL 17.5
CREATE TABLE Orders (
    order_id INTEGER DEFAULT nextval('order_id_seq') PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

When inserting a record, the order_id value will be automatically filled:

PostgreSQL 17.5
INSERT INTO Orders (customer_name, order_date)
VALUES ('John Doe', '2025-11-14');

Method 2: SERIAL Type

PostgreSQL offers a convenient SERIAL type that automatically creates a sequence: 💡

PostgreSQL 17.5
CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

This is equivalent to:

PostgreSQL 17.5
CREATE SEQUENCE products_product_id_seq;

CREATE TABLE Products (
    product_id INTEGER DEFAULT nextval('products_product_id_seq') PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

Managing Sequences

• Viewing Sequence Information

  PostgreSQL 17.5
  SELECT * FROM information_schema.sequences
  WHERE sequence_name = 'order_id_seq';
  

  Or in more detail via system function:

  PostgreSQL 17.5
  SELECT last_value, is_called FROM order_id_seq;
  

• Modifying Sequence Parameters

  PostgreSQL 17.5
  ALTER SEQUENCE order_id_seq
      INCREMENT BY 10
      RESTART WITH 2000;
  

• Deleting a Sequence

  PostgreSQL 17.5
  DROP SEQUENCE order_id_seq;
  

  ⚠️ Warning: If the sequence is used in a table as a default value, you need to remove this constraint first or use CASCADE:

  PostgreSQL 17.5
  DROP SEQUENCE order_id_seq CASCADE;
  

Sequences for Multiple Tables

A single sequence can be used for multiple tables. This is especially useful for document management systems where continuous numbering is needed:

PostgreSQL 17.5
CREATE SEQUENCE document_number_seq START WITH 100000;

CREATE TABLE Invoices (
    invoice_id SERIAL PRIMARY KEY,
    document_number INTEGER DEFAULT nextval('document_number_seq') UNIQUE,
    customer_id INTEGER,
    amount DECIMAL(10, 2)
);

CREATE TABLE DeliveryNotes (
    note_id SERIAL PRIMARY KEY,
    document_number INTEGER DEFAULT nextval('document_number_seq') UNIQUE,
    order_id INTEGER,
    delivery_date DATE
);

CREATE TABLE Acts (
    act_id SERIAL PRIMARY KEY,
    document_number INTEGER DEFAULT nextval('document_number_seq') UNIQUE,
    contract_id INTEGER,
    act_date DATE
);

Now all documents (invoices, delivery notes, acts) will have unique numbers across the entire document management system. 🌐

Advanced Techniques

• Sequence with Caching

  For improved performance, you can use caching:

  PostgreSQL 17.5
  CREATE SEQUENCE fast_seq
      START WITH 1
      INCREMENT BY 1
      CACHE 100;
  

  The database will pre-allocate 100 values in memory, which speeds up their retrieval. ⚡

• Cyclic Sequence

  For cases when cyclic numbering is needed (e.g., days of the week):

  PostgreSQL 17.5
  CREATE SEQUENCE day_of_week_seq
      START WITH 1
      INCREMENT BY 1
      MINVALUE 1
      MAXVALUE 7
      CYCLE;
  

  After reaching 7, the sequence returns to 1. 🔄

• Descending Sequence

  PostgreSQL 17.5
  CREATE SEQUENCE countdown_seq
      START WITH 100
      INCREMENT BY -1
      MINVALUE 1
      NO CYCLE;
  

  This sequence will generate: 100, 99, 98, 97... ⬇️

Things to Watch Out For

• 90% of the time, SERIAL is all you need
  Don't create a separate sequence unless you need its specific flexibility. SERIAL is simpler and handles most tasks just fine.

• Sequences have gaps 🕳️
  Don't expect numbers to go without holes: 1, 2, 3, 4... Transaction rollbacks, parallel queries, caching — all create gaps. If you see "order #102 after #99" — that's normal.

  If you truly need continuous numbering (like for invoices per accounting requirements), sequences aren't the best choice.

• Forgotten MAXVALUE is a time bomb 💥
  Set MAXVALUE 999999 and forgot about it? After the millionth record, everything stops. For production, always use the maximum possible value or monitor approaching the limit.

Summing Up

For most tasks, SERIAL works great. But if you need shared numbering for multiple tables, a specific increment step, or the ability to get an ID before insertion — sequences solve these problems elegantly.

Just remember about number gaps and watch your MAXVALUE. Otherwise, it's a simple and powerful tool! 🚀