PostgreSQL Sequences vs Identity Columns
On this page
SERIAL vs identity columns
Every table with a generated id gets its numbers from a sequence, a small database object that hands out increasing numbers safely to many sessions at once. You choose how the column is wired to it. There are three options:
| `serial` / `bigserial` | `GENERATED BY DEFAULT AS IDENTITY` | `GENERATED ALWAYS AS IDENTITY` | |
|---|---|---|---|
| SQL standard | No, PostgreSQL shorthand | Yes (PostgreSQL 10+) | Yes (PostgreSQL 10+) |
| Insert your own id | Silently accepted | Silently accepted | Rejected unless you write OVERRIDING SYSTEM VALUE |
| Extra grants needed | USAGE on the sequence | No | No |
| Recommended for new tables | No | When you often load ids yourself | Yes, the default choice |
serial isn't a real type. It creates an integer column, a sequence, and a column default of nextval('...'). It still works, and you'll see it in a lot of existing code. Identity columns do the same job as a proper column property that tools understand.
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
INSERT INTO customers (id, name) VALUES (999, 'Acme');
-- ERROR: cannot insert a non-DEFAULT value into column "id"
INSERT INTO customers (id, name) OVERRIDING SYSTEM VALUE VALUES (999, 'Acme'); -- allowed, on purposeThat error is the point of ALWAYS. The usual way an id column breaks is application code supplying its own ids that later collide with the sequence. ALWAYS turns that into an immediate error.
Choose the column type deliberately, because the identity uses whatever type you declare. integer runs out at about 2.1 billion, and changing a busy table's key from integer to bigint later is a painful migration. For tables that could grow large, start with bigint.
How sequences work: nextval and currval
You can create and use sequences directly, for things like order numbers that aren't primary keys:
CREATE SEQUENCE order_number_seq START WITH 1000;
SELECT nextval('order_number_seq'); -- 1000
SELECT nextval('order_number_seq'); -- 1001
SELECT currval('order_number_seq'); -- 1001, the last value this session gotnextval advances the sequence and returns the new value. currval returns the value your own session last received, so it's safe even with other sessions inserting at the same time. In application code, INSERT ... RETURNING id (Day 6) is simpler still.
Identity columns accept the same options in parentheses, such as GENERATED ALWAYS AS IDENTITY (START WITH 10000).
Fixing the duplicate-key error with setval
Here's the most common sequence problem in practice. You import data with explicit ids, using OVERRIDING SYSTEM VALUE, a restore script or a CSV load. Then the next normal insert fails:
INSERT INTO customers (id, name) OVERRIDING SYSTEM VALUE VALUES (1, 'Imported A'), (2, 'Imported B');
INSERT INTO customers (name) VALUES ('New customer');
-- ERROR: duplicate key value violates unique constraint "customers_pkey"The sequence keeps its own counter. Explicit ids never called nextval, so the sequence still thinks it should hand out 1. Move it past the highest id in the table:
SELECT setval(pg_get_serial_sequence('customers', 'id'),
(SELECT max(id) FROM customers));
INSERT INTO customers (name) VALUES ('New customer'); -- works, gets 1000pg_get_serial_sequence finds the sequence behind a column, for serial and identity columns alike, so you don't have to guess its name. setval(seq, n) means "n has been used, hand out n + 1 next". With a third argument of false, the next value is n itself. Getting that backwards is a classic off-by-one after migrations.
For a test table you want to empty and restart, TRUNCATE customers RESTART IDENTITY resets the sequence too.
Why your ids have gaps
Look at almost any production table and the ids skip numbers. That's normal, and it's not missing data. Gaps come from:
- Rollbacks.
nextvalis deliberately not transactional. If a transaction takes 501 and rolls back, 501 is never reused. Giving numbers back would mean making every other session wait until that transaction finished, which would defeat the point of a fast counter. - Failed inserts. An insert that hits a constraint error has already used its number.
- Caching and crashes. A sequence can hand out numbers in blocks to reduce contention (the CACHE option). Unused numbers in a block are lost when the session ends or the server crashes.
- Deletes. Obviously.
Sometimes a business rule demands numbers with no gaps, such as invoice numbers in some countries. A sequence can't guarantee that. Use a counter table and update it inside the same transaction as the invoice:
CREATE TABLE invoice_counter (last_no bigint NOT NULL);
INSERT INTO invoice_counter VALUES (0);
UPDATE invoice_counter SET last_no = last_no + 1 RETURNING last_no;The UPDATE locks the counter row until the transaction ends, so invoice numbers are strictly sequential, and a rollback undoes the increment too. The price is that invoice creation happens one at a time. That's fine for invoices, but don't do it for high-volume ids.
Check yourself
SELECT setval(pg_get_serial_sequence('table', 'id'), (SELECT max(id) FROM table));. The import didn't advance the sequence, so it was handing out ids that already exist.
Sequence values used by transactions that rolled back or inserts that failed are never reused. Gaps are expected. Check the audit log or application logs before assuming data was deleted.
Use bigint GENERATED ALWAYS AS IDENTITY. It's the SQL standard, rejects accidental manual ids, and bigint won't run out the way a 4-byte integer can.
nextval is deliberately not transactional, so concurrent transactions never wait on each other for ids. A rolled-back insert still consumed its value, which is why gaps are normal. If the business needs gap-free numbers, such as legal invoice numbers, generate them separately with a locked counter table.
ALWAYS rejects inserts that supply their own id unless they explicitly use OVERRIDING SYSTEM VALUE, which prevents accidental manual ids from colliding with the sequence later. BY DEFAULT silently accepts supplied ids, which is how the duplicate-key problem after a data import usually starts.