22 Identity Columns and Sequences

You rarely want to hand-pick primary key values. PostgreSQL gives you two ways to generate them automatically: the modern, SQL-standard GENERATED ... AS IDENTITY syntax, and the older serial / bigserial shortcuts. Both are powered by a sequence — an independent object that hands out incrementing numbers. IDENTITY is the recommended choice for new code; serial is still common in older codebases.

What you’ll learn

• The GENERATED ALWAYS AS IDENTITY syntax
• The differences from the older serial shortcut
• What a sequence is and how to inspect it
• Why generated IDs can have gaps
• Resetting a sequence after a bulk import

Example

-- Modern style: GENERATED AS IDENTITY (recommended)
DROP TABLE IF EXISTS messages_example;
CREATE TABLE messages_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO messages_example (body) VALUES ('first'), ('second'), ('third');
SELECT id, body FROM messages_example ORDER BY id;

-- Legacy serial shortcut (rewrites to a sequence + DEFAULT under the hood)
DROP TABLE IF EXISTS legacy_example;
CREATE TABLE legacy_example (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO legacy_example (name) VALUES ('a'), ('b');
SELECT id, name FROM legacy_example ORDER BY id;

-- Inspect sequences
SELECT sequencename, last_value, increment_by
FROM pg_sequences
WHERE sequencename IN ('messages_example_id_seq', 'legacy_example_id_seq');

-- Manually grab the next value (and consume it)
SELECT nextval('messages_example_id_seq') AS next_id;
SELECT currval('messages_example_id_seq') AS curr_id;

-- Reset a sequence (e.g. after a bulk import that supplied IDs)
SELECT setval('legacy_example_id_seq',
              (SELECT max(id) FROM legacy_example));

GENERATED ALWAYS AS IDENTITY tells PostgreSQL the column gets its value from the underlying sequence and cannot be overridden by INSERT (unless you use OVERRIDING SYSTEM VALUE). GENERATED BY DEFAULT AS IDENTITY is the more permissive variant — if the INSERT provides a value, it is used; otherwise the sequence supplies one. Use ALWAYS when you want to prevent accidental ID injection from application code; use BY DEFAULT when you want flexibility for migrations and tests.

The older serial (integer + sequence) and bigserial (bigint + sequence) are syntactic sugar for integer NOT NULL DEFAULT nextval('seq'). They still work, but IDENTITY is the SQL standard, plays better with pg_dump, and tracks ownership of the sequence more cleanly.

Sequences are independent of transactions: nextval() does not roll back. That means if a transaction inserts a row and rolls back, the ID is burned — the next row gets the value after it. IDs can have gaps, and your application should never assume they are contiguous.

Run the example

To run:

$ psql -f source/identity-and-sequences.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id | body
----+--------
  1 | first
  2 | second
  3 | third
(3 rows)
 ...

Common pitfalls

• Reading “the last ID I just inserted” using currval() from a different connection — it won’t work. Sequences are shared objects; only the value remembered by currval() is per-session. Use RETURNING id instead.
• After a bulk import that supplied explicit IDs, the sequence is still at its old value, so the next auto-generated ID collides. Reset it: SELECT setval('t_id_seq', (SELECT max(id) FROM t));.
• Counting on IDs to be sequential or to reflect insertion order — they don’t, especially under concurrency. Sort by created_at, id if you need order.
• Using integer (32-bit) for an IDENTITY column on a high-volume table — you can exhaust the range. Use bigint.

Downloads

SQL file: identity-and-sequences.sql · All lesson files · Setup guide