PostgreSQL 428C9 Error: Causes and Solutions Complete Guide

PostgreSQL Error 428C9: generated always Explained

PostgreSQL error code 428C9 occurs when you attempt to insert or update a value directly into a column defined as GENERATED ALWAYS AS IDENTITY or GENERATED ALWAYS AS (expression) STORED. These columns are exclusively managed by PostgreSQL's internal engine, and any attempt to override them without the proper syntax will be rejected immediately. This is a deliberate design choice to ensure data integrity for system-managed columns.

---

Top 3 Causes

1. Inserting an Explicit Value into a GENERATED ALWAYS AS IDENTITY Column

This is the most common cause. When migrating data or using legacy application code that explicitly provides an ID value, PostgreSQL will throw 428C9.

CREATE TABLE customers (
    customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

-- ❌ Triggers 428C9
INSERT INTO customers (customer_id, name) VALUES (1, 'Alice');

-- ✅ Fix: Omit the identity column
INSERT INTO customers (name) VALUES ('Alice');

-- ✅ Fix: Use OVERRIDING SYSTEM VALUE when you must specify an ID
INSERT INTO customers (customer_id, name)
OVERRIDING SYSTEM VALUE
VALUES (1, 'Alice');

-- ✅ After bulk insert, resync the sequence
SELECT setval(
    pg_get_serial_sequence('customers', 'customer_id'),
    (SELECT MAX(customer_id) FROM customers),
    true
);

---

2. Directly Updating a GENERATED ALWAYS AS (expr) STORED Column

Generated columns are computed automatically from other columns. You cannot update them directly — you must update the source columns instead.

CREATE TABLE invoice_items (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_price NUMERIC(10,2),
    qty INT,
    line_total NUMERIC(10,2) GENERATED ALWAYS AS (unit_price * qty) STORED
);

-- ❌ Triggers 428C9
UPDATE invoice_items SET line_total = 200.00 WHERE id = 1;

-- ✅ Fix: Update the source columns; line_total recalculates automatically
UPDATE invoice_items SET unit_price = 40.00, qty = 5 WHERE id = 1;

-- Verify
SELECT id, unit_price, qty, line_total FROM invoice_items WHERE id = 1;
-- line_total will be 200.00 automatically

---

3. Data Migration or Restore Conflicts

When restoring a pg_dump backup or running ETL pipelines into a table that uses GENERATED ALWAYS, the restore script may try to insert original ID values and fail.

-- ✅ Fix: Temporarily switch to GENERATED BY DEFAULT before migration
ALTER TABLE customers
    ALTER COLUMN customer_id SET GENERATED BY DEFAULT;

-- Now bulk-insert with explicit IDs
INSERT INTO customers (customer_id, name)
SELECT customer_id, name FROM legacy_customers;

-- Restore ALWAYS constraint after migration
ALTER TABLE customers
    ALTER COLUMN customer_id SET GENERATED ALWAYS;

-- Resync sequence to avoid future conflicts
SELECT setval(
    pg_get_serial_sequence('customers', 'customer_id'),
    (SELECT MAX(customer_id) FROM customers),
    true
);

---

Quick Fix Summary

Situation	Solution
Must insert a specific ID	Use OVERRIDING SYSTEM VALUE
Want auto-generated ID	Omit the column from INSERT
Need to change a generated column	Update the source expression columns
Migration / restore failure	Temporarily use SET GENERATED BY DEFAULT

---

Prevention Tips

1. Choose GENERATED BY DEFAULT when external control is possible. If your application, ORM, or migration tooling may ever need to supply explicit values, use GENERATED BY DEFAULT AS IDENTITY instead of ALWAYS. Reserve GENERATED ALWAYS strictly for columns that must never be manually set, such as internal audit trail IDs.

2. Configure your ORM to exclude generated columns. In frameworks like Hibernate or SQLAlchemy, mark GENERATED ALWAYS columns with insertable=False, updatable=False to prevent the ORM from including them in DML statements. Add linting or CI checks to catch any direct writes to these columns before they reach production.

---

Related Errors

• 42P10 — Invalid column reference inside a generated column expression.
• 23505 — Unique violation, often follows OVERRIDING SYSTEM VALUE if the sequence isn't resynced after a manual insert.
• 42601 — Syntax error in the GENERATED ALWAYS AS expression definition.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.