Incident brief
Cannot insert a non-DEFAULT value into a GENERATED ALWAYS column
An INSERT supplied an explicit value for a GENERATED ALWAYS AS IDENTITY column. PostgreSQL only accepts a user value there when the statement says OVERRIDING SYSTEM VALUE.
What lands in your log
ERROR: cannot insert a non-DEFAULT value into column "id"
In 10 seconds
- What triggers it
- Create a table with an id column declared GENERATED ALWAYS AS IDENTITY.
- Fix
- Omit the identity column from the INSERT and let the sequence assign it.
- Proof
- Reproduced on PostgreSQL 16.14 → Inserting an explicit id into a GENERATED ALWAYS AS IDENTITY column was rejected with SQLSTATE 428C9. Omitting id and using OVERRIDING SYSTEM VALUE both worked.
Fix
What to do right now
Application-level steps for this error.
- Omit the identity column from the INSERT and let the sequence assign it.
- If you truly must supply the value (data migration, backfill), add OVERRIDING SYSTEM VALUE.
- If the app should normally set the value, declare the column GENERATED BY DEFAULT AS IDENTITY instead of ALWAYS.
-- Omit the GENERATED ALWAYS identity column.
INSERT INTO invoices(amount) VALUES (42.00)
RETURNING id, amount;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Inspect whether the column is identity or generated and omit it (or use the explicit identity override only when intentional).
Identity and generated columns
Find columns PostgreSQL owns and their generation expression/default.
SELECT a.attrelid::regclass AS table_name, a.attname,
a.attidentity, a.attgenerated,
pg_get_expr(d.adbin, d.adrelid) AS generation_or_default
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attnum > 0 AND NOT a.attisdropped
AND (a.attidentity <> '' OR a.attgenerated <> '')
ORDER BY a.attrelid::regclass::text, a.attnum;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 16 Documentation, CREATE TABLE: GENERATED … AS IDENTITY
In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence.Read the full section on postgresql.org →
Supplying an explicit value for the identity column
id is GENERATED ALWAYS, so PostgreSQL reserves it for the identity sequence. Supplying an explicit value without OVERRIDING SYSTEM VALUE raises SQLSTATE 428C9 and inserts nothing.The session is unaffected by the error
The rejected INSERT ran outside a transaction block, so the connection is untouched and the next statement returns normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Create a table with an id column declared GENERATED ALWAYS AS IDENTITY.
- 2INSERT a row that supplies an explicit value for id.
- 3PostgreSQL raises SQLSTATE 428C9 and inserts nothing.
One client: a table whose id is GENERATED ALWAYS AS IDENTITY, and an INSERT that supplies id explicitly.
CREATE TABLE invoices (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric
);INSERT INTO invoices (id, amount) VALUES (100, 42.00);SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLEERROR: cannot insert a non-DEFAULT value into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override. session_after_error
---------------------
ok
(1 row)Both documented paths, omitting the identity column, and OVERRIDING SYSTEM VALUE, were executed and produced rows, confirming the manual's rule for GENERATED ALWAYS.
Without this
Above: supplying id directly raises SQLSTATE 428C9.
With this, tested
Below: omitting id, and OVERRIDING SYSTEM VALUE, both insert successfully.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.