DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 428C9 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 428C9: GENERATED ALWAYS Explained

PostgreSQL error code 428C9 occurs when you attempt to manually insert or update a value in a column defined as GENERATED ALWAYS AS IDENTITY or GENERATED ALWAYS AS (expression) STORED. These columns are designed to have their values controlled exclusively by PostgreSQL's internal mechanisms, and any attempt to override them without proper syntax will be rejected. This error is especially common during data migrations, ORM misconfiguration, and when transitioning from SERIAL to IDENTITY columns.

Top 3 Causes

1. Inserting Directly into a GENERATED ALWAYS AS IDENTITY Column

The most frequent cause. When you explicitly provide a value for an identity column defined with GENERATED ALWAYS, PostgreSQL will immediately reject the statement.

-- Table definition
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT,
    amount NUMERIC
);

-- This will trigger error 428C9
INSERT INTO orders (order_id, product_name, amount)
VALUES (100, 'Laptop', 1500.00);
-- ERROR: cannot insert into column "order_id"
-- DETAIL: Column "order_id" is an identity column defined as GENERATED ALWAYS.

-- Correct approach: omit the identity column
INSERT INTO orders (product_name, amount)
VALUES ('Laptop', 1500.00);
Enter fullscreen mode Exit fullscreen mode

2. Attempting to UPDATE a Generated Stored Column

Columns defined with GENERATED ALWAYS AS (expression) STORED are computed automatically from an expression. Any direct UPDATE targeting these columns will fail with 428C9.

-- Table with a generated stored column
CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price NUMERIC,
    tax_rate NUMERIC DEFAULT 0.1,
    price_with_tax NUMERIC GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

-- This will fail
UPDATE products SET price_with_tax = 9999.00 WHERE product_id = 1;
-- ERROR: column "price_with_tax" can only be updated to DEFAULT

-- Correct approach: update the source columns only
UPDATE products SET price = 2000.00 WHERE product_id = 1;
-- price_with_tax is recalculated automatically
Enter fullscreen mode Exit fullscreen mode

3. Data Migration Without OVERRIDING SYSTEM VALUE

When migrating data from another database or restoring from a backup, you often need to preserve original ID values. Using a plain INSERT with explicit IDs on a GENERATED ALWAYS column will fail.

-- Migration attempt that fails
INSERT INTO orders (order_id, product_name, amount)
VALUES (500, 'Keyboard', 75.00);
-- ERROR: 428C9

-- Correct approach for migration: use OVERRIDING SYSTEM VALUE
INSERT INTO orders (order_id, product_name, amount)
OVERRIDING SYSTEM VALUE
VALUES (500, 'Keyboard', 75.00);

-- After migration, reset the sequence to avoid future conflicts
SELECT setval(
    pg_get_serial_sequence('orders', 'order_id'),
    (SELECT MAX(order_id) FROM orders)
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option A — Use OVERRIDING SYSTEM VALUE (when you must insert a specific value):

INSERT INTO orders (order_id, product_name, amount)
OVERRIDING SYSTEM VALUE
VALUES (100, 'Monitor', 800.00);
Enter fullscreen mode Exit fullscreen mode

Option B — Switch to GENERATED BY DEFAULT (when external systems need to supply values):

ALTER TABLE orders
ALTER COLUMN order_id SET GENERATED BY DEFAULT;

-- Now both explicit and auto-generated inserts work
INSERT INTO orders (order_id, product_name, amount) VALUES (200, 'Mouse', 25.00);
INSERT INTO orders (product_name, amount) VALUES ('Headset', 99.00);
Enter fullscreen mode Exit fullscreen mode

Option C — Inspect generated columns before writing queries:

SELECT column_name, identity_generation, is_generated, generation_expression
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
  AND (identity_generation IS NOT NULL OR is_generated = 'ALWAYS');
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Establish a clear IDENTITY column strategy at design time. Use GENERATED ALWAYS only for purely internal surrogate keys where no external system will ever need to supply a value. For tables involved in ETL, migrations, or cross-system integrations, prefer GENERATED BY DEFAULT AS IDENTITY from the start to avoid painful schema alterations later.

  2. Validate migration scripts in staging before production. Always run full data migration scripts against a staging environment that mirrors production schema. Add a checklist item to verify that any GENERATED ALWAYS columns are handled with OVERRIDING SYSTEM VALUE and that sequences are reset correctly after bulk inserts. Automating this check in your CI/CD pipeline will prevent unexpected production outages.

Related Errors

Code Name Brief Description
42601 syntax_error Malformed GENERATED ALWAYS AS expression syntax
42P10 invalid_column_reference Referencing another generated column inside a generation expression
0A000 feature_not_supported Using non-deterministic functions like now() or random() in a generated column expression

📖 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.

Top comments (0)