Sequences

A sequence is a standalone database object whose only job is generating a series of numbers, one at a time, guaranteed never to repeat, in a strictly increasing (or decreasing) order. It sounds simple, and it is — but it is also the mechanism that quietly powers SERIAL and IDENTITY columns behind the scenes, and it is a fully usable object in its own right, independent of any particular table or column.

Creating and using a sequence directly

A standalone sequence

SQL

CREATE SEQUENCE order_number_seq START WITH 1000 INCREMENT BY 1;

SELECT nextval('order_number_seq'); -- 1000
SELECT nextval('order_number_seq'); -- 1001
SELECT currval('order_number_seq'); -- 1001 (last value returned in this session)

nextval() advances the sequence and returns the new value — every call is guaranteed to hand out a number no other call will ever receive again, even under heavy concurrent access, because sequence advancement does not participate in normal transaction locking. currval() returns whatever value was last obtained by this session, without advancing anything; it errors if this session has never called nextval() on that sequence yet.

setval(): jumping the sequence to a specific value

SQL

-- Force the next nextval() call to return 5000
SELECT setval('order_number_seq', 4999);
SELECT nextval('order_number_seq'); -- 5000

How SERIAL and IDENTITY are actually implemented

The SERIAL & Identity Columns page covers declaring an auto-incrementing column, but it is worth demystifying what actually happens underneath. SERIAL is not a real column type — it is shorthand that PostgreSQL expands, at table-creation time, into an INTEGER column plus a dedicated sequence plus a DEFAULT that calls nextval() on it.

What CREATE TABLE ... SERIAL expands into

SQL

CREATE TABLE orders (order_id SERIAL PRIMARY KEY);

-- is expanded by PostgreSQL into roughly:
CREATE SEQUENCE orders_order_id_seq;
CREATE TABLE orders (
  order_id INTEGER NOT NULL DEFAULT nextval('orders_order_id_seq') PRIMARY KEY
);
ALTER SEQUENCE orders_order_id_seq OWNED BY orders.order_id;

The modern GENERATED ... AS IDENTITY syntax does the same thing under the hood — creating and owning a sequence — but is standard SQL and integrates more cleanly with permissions and pg_dump. Either way, "auto-increment" in PostgreSQL is never magic: it is always a plain sequence, called through nextval(), attached as a column default. You can inspect it directly with pg_get_serial_sequence().

Finding the sequence behind a column

SQL

SELECT pg_get_serial_sequence('orders', 'order_id');

 pg_get_serial_sequence
-------------------------
 public.orders_order_id_seq

Using a sequence independently of any table

Because a sequence is not tied to a column, it can generate numbers shared across multiple related tables — something a per-table SERIAL/IDENTITY column cannot do on its own. A common case is a single, globally unique order number that needs to be consistent whether the order originated from a web checkout table or a phone-order table.

One sequence feeding two different tables

SQL

CREATE SEQUENCE order_number_seq;

CREATE TABLE web_orders (
  order_id     INTEGER PRIMARY KEY,
  order_number BIGINT NOT NULL DEFAULT nextval('order_number_seq'),
  total_amount NUMERIC(10, 2) NOT NULL
);

CREATE TABLE phone_orders (
  order_id     INTEGER PRIMARY KEY,
  order_number BIGINT NOT NULL DEFAULT nextval('order_number_seq'),
  total_amount NUMERIC(10, 2) NOT NULL
);

-- order_number values are guaranteed unique across both tables

Resetting a sequence

ALTER SEQUENCE ... RESTART WITH resets the sequence's next value, most commonly used after a bulk data load or when resetting a test/staging environment back to a known starting point.

Resetting a sequence

SQL

ALTER SEQUENCE order_number_seq RESTART WITH 1;

Gaps in a sequence are normal, not a bug

A rolled-back transaction, a crashed connection, or simple concurrent access all leave gaps in a sequence's output — a sequence guarantees uniqueness and increasing order, not that every number gets used. If a business requirement genuinely needs gap-free numbering (some invoicing regulations do), that has to be built with explicit application logic and locking, not a plain sequence.

A sequence generates a unique, ordered series of numbers, independent of any table.
nextval() advances and returns the next value; currval() re-reads the last value this session got; setval() jumps the sequence to a specific point.
SERIAL and IDENTITY columns are just a sequence plus a DEFAULT nextval() — no separate magic underneath.
A single sequence can feed unique numbers across several unrelated tables.
ALTER SEQUENCE ... RESTART WITH resets the next value.