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.
In 10 seconds
- What
- Cannot insert a non-DEFAULT value into a GENERATED ALWAYS column
- What triggers it
- Create a table with an id column declared GENERATED ALWAYS AS IDENTITY.
- The 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.
The fix
What to do right now
The immediate, application-level response to 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.
-- let the identity column assign itself, or override explicitly
INSERT INTO invoices (amount) VALUES (42.00);
INSERT INTO invoices (id, amount) OVERRIDING SYSTEM VALUE VALUES (100, 99.00);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;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.
Insert one row that lets the sequence assign id, and one that forces id with OVERRIDING SYSTEM VALUE.
Without this
Above: supplying id directly raises SQLSTATE 428C9.
With this, tested
Below: omitting id, and OVERRIDING SYSTEM VALUE, both insert successfully.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed