The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Protect an identity column | id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY | View examples |
| Allow identity overrides | id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY | View examples |
| Configure the identity sequence | GENERATED ALWAYS AS IDENTITY (START
WITH 1000 INCREMENT BY 1 CACHE 20
) | View examples |
| Import explicit identity values | INSERT INTO app.accounts OVERRIDING SYSTEM VALUE (id, name)
VALUES (42, 'Ada'); | View examples |
| Regenerate imported identities | INSERT INTO app.accounts OVERRIDING USER VALUE
SELECT *
FROM staging.accounts; | View examples |
| Store a generated value | total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED | View examples |
| Compute a virtual value | full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL | View examples |
| Allocate a sequence value | SELECT nextval('app.ticket_number_seq'::regclass); | View examples |
| Read this session's value | SELECT currval('app.ticket_number_seq'::regclass); | View examples |
| Create an owned sequence | CREATE SEQUENCE app.ticket_number_seq AS bigint CACHE 20 OWNED BY app.tickets.number; | View examples |
| Grant allocation access | GRANT USAGE
ON SEQUENCE app.ticket_number_seq TO app_writer; | View examples |
| Find an attached sequence | SELECT pg_get_serial_sequence('app.accounts', 'id'); | View examples |
| Plan a sequence repair | SELECT setval(pg_get_serial_sequence('app.accounts','id'), max(id), true)
FROM app.accounts; | View examples |
| Inspect generated attributes | SELECT column_name,
is_identity,
identity_generation,
is_generated
FROM information_schema.columns
WHERE table_schema = 'app'; | View examples |
Identity columns, generated columns, and sequences solve different automation problems. Identity columns attach a sequence-backed default to a column, generated columns derive a value from the current row, and standalone sequences allocate concurrent numbers independently of transactions. Choose by semantics, add explicit uniqueness where required, and treat overrides or sequence resets as controlled migration operations.
Step by step
Detailed examples
Use identity columns for sequence-backed table keys
GENERATED ALWAYS prevents accidental explicit values, while BY DEFAULT permits them and is useful only when overriding is part of the data contract. Identity implies NOT NULL but not uniqueness, so add PRIMARY KEY or UNIQUE separately. Partitions inherit the partitioned table's identity properties; ordinary inheritance does not copy them automatically.
CREATE TABLE app.accounts (
id bigint GENERATED ALWAYS AS IDENTITY
(START WITH 1000 CACHE 20),
email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT accounts_pkey PRIMARY KEY (id),
CONSTRAINT accounts_email_key UNIQUE (email)
);Make identity overrides visible in migration code
OVERRIDING SYSTEM VALUE is required to insert an explicit value into a GENERATED ALWAYS identity. OVERRIDING USER VALUE does the opposite: it ignores supplied identity values and generates replacements. Explicit imports do not automatically advance the underlying sequence, so reconcile it after loading and before concurrent writers resume.
-- Preserve verified legacy keys.
INSERT INTO app.accounts OVERRIDING SYSTEM VALUE (id, email)
SELECT legacy_id, email FROM staging.accounts;
-- Or generate fresh destination keys.
INSERT INTO app.accounts OVERRIDING USER VALUE
SELECT * FROM staging.accounts;Derive same-row values with generated columns
A generated expression may use only immutable functions, cannot contain subqueries, and cannot reference another generated column. STORED values are computed on writes; in PostgreSQL 18, VIRTUAL values are computed on reads and are the default, but virtual expressions have additional restrictions on user-defined types and functions. Generated columns cannot also have a default or identity and cannot be written except with DEFAULT.
CREATE TABLE app.invoice_lines (
invoice_id bigint NOT NULL,
quantity numeric NOT NULL CHECK (quantity > 0),
unit_price numeric NOT NULL CHECK (unit_price >= 0),
line_total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED
);
INSERT INTO app.invoice_lines (invoice_id, quantity, unit_price)
VALUES (101, 2, 19.95);Treat sequence allocation as nontransactional
nextval is atomic across sessions, but values consumed by aborted transactions or ON CONFLICT attempts are not reclaimed. currval is session-local and errors until that session has called nextval for the sequence. setval changes are visible immediately and are not rolled back. Sequences therefore provide distinct allocation, not gapless numbering or commit-order timestamps.
SELECT nextval('app.ticket_number_seq'::regclass) AS allocated;
SELECT currval('app.ticket_number_seq'::regclass) AS same_session_value;Use standalone sequences for explicit allocation contracts
A standalone sequence can serve multiple statements or tables, but sharing increases coupling. OWNED BY links its lifecycle to one column. CACHE reduces sequence contention but can create larger gaps and values may appear out of order across sessions. Grant USAGE for ordinary nextval/currval callers; UPDATE is needed for setval and should remain administrative.
CREATE SEQUENCE app.ticket_number_seq AS bigint CACHE 20;
ALTER TABLE app.tickets
ALTER COLUMN number SET DEFAULT nextval('app.ticket_number_seq'::regclass);
ALTER SEQUENCE app.ticket_number_seq OWNED BY app.tickets.number;
GRANT USAGE ON SEQUENCE app.ticket_number_seq TO app_writer;Repair sequence position only under a controlled write boundary
After explicit-ID imports, determine the attached sequence with pg_get_serial_sequence and compare table keys with sequence state. Do not run max(id)-based setval while concurrent inserts can allocate values. For an empty table, setval needs an explicitly chosen initial value and is_called setting; blindly using max(id) yields null and does not repair anything.
-- Run only after blocking concurrent writers and verifying the imported maximum.
SELECT pg_get_serial_sequence('app.accounts', 'id') AS sequence_name;
SELECT max(id) AS imported_max FROM app.accounts;
SELECT setval(
pg_get_serial_sequence('app.accounts', 'id'),
(SELECT max(id) FROM app.accounts),
true
);Audit metadata and portability before deployment
Use information_schema.columns and pg_sequences to inventory generation rules and sequence configuration. Virtual generated columns are a PostgreSQL 18 addition, so use STORED or another design when supporting older majors. Test dump/restore, replication publication settings for stored generated columns, privileges on expression functions, and sequence exhaustion alarms as part of deployment.
SELECT table_schema, table_name, column_name, is_identity,
identity_generation, is_generated, generation_expression
FROM information_schema.columns
WHERE table_schema = 'app'
AND (is_identity = 'YES' OR is_generated = 'ALWAYS')
ORDER BY table_name, ordinal_position;
SELECT schemaname, sequencename, data_type, start_value, max_value, cache_size
FROM pg_sequences
WHERE schemaname = 'app'
ORDER BY sequencename;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Identity Columnspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Generated Columnspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Sequence Manipulation Functionspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE SEQUENCEpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE TABLEpostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.