PostgreSQL Constraints and Error Messages

Schemas and Data / Guide 7 of 47 / Beginner

• OSUbuntu 26.04 LTS (resolute)
• PostgreSQL18.6-0ubuntu0.26.04.1
• Server timezoneEtc/UTC
• TimeAbout 15 min
• Reviewed27 August 2026

Written against the versions above. PostgreSQL has no permissive mode. There is no equivalent of MySQL's `sql_mode` to relax, which means these refusals cannot be configured away.

Before you start

• The customers table from the previous guide, with its three rows.

1. 1

   Exceed a numeric column's precision

   numeric(10,2) is ten significant digits with two after the point. The insert supplies twelve.

   ERROR: numeric field overflow, and then the line that matters: DETAIL: A field with precision 10, scale 2 must round to an absolute value less than 10^8.

   That DETAIL states the rule rather than the symptom. It tells you the actual limit is 10^8, which is not obvious from (10,2) and is exactly the arithmetic people get wrong.

   Worth noting for anyone arriving from MySQL: nothing was configured to make this an error. MySQL needs STRICT_TRANS_TABLES and silently truncates without it. PostgreSQL has no such mode - this is the only behaviour it has.

   $psql -d appdb -c "INSERT INTO customers (name, email, credit) VALUES ('Too Big','big@example.com',99999999999.99)" ; echo "exit=$?"ERROR:  numeric field overflowDETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.exit=1

   Expected resultERROR: numeric field overflow with the 10^8 DETAIL, and exit=1.

   Success conditionYou have seen a refusal that explains the rule it enforced.

2. 2

   Insert a duplicate

   ERROR: duplicate key value violates unique constraint "customers_email_key" and DETAIL: Key (email)=(ada@example.com) already exists.

   Two useful facts in two lines: which constraint, and which value. On a table with several unique constraints, the name tells you immediately which rule was broken - and because PostgreSQL names constraints predictably, customers_email_key is readable without looking anything up.

   This is the error behind most failed signups, and catching it is the correct way to handle a race - the check-then-insert pattern in application code cannot be made safe.

   $psql -d appdb -c "INSERT INTO customers (name, email) VALUES ('Ada Again','ada@example.com')" ; echo "exit=$?"ERROR:  duplicate key value violates unique constraint "customers_email_key"DETAIL:  Key (email)=(ada@example.com) already exists.exit=1

   Expected resultThe constraint named and the conflicting value shown.

   Success conditionYou can identify which unique constraint rejected a row.

3. 3

   Omit a NOT NULL column

   ERROR: null value in column "name" of relation "customers" violates not-null constraint - and then the DETAIL prints the entire failing row.

   Failing row contains (5, null, noname@example.com, 0.00, {}, {}, 2026-08-27 13:59:03...).

   Read what that shows. Every column, including the ones you did not supply, already filled with their defaults - the identity value 5 was allocated, credit defaulted to 0.00, the array and jsonb to empty, the timestamp to now. The row was fully constructed and then rejected at the last moment.

   For debugging a failing bulk insert this is enormously more useful than a column name alone. Be aware of the flip side: that DETAIL goes into the server log, so a failing insert of sensitive data writes it there in clear text.

   $psql -d appdb -c "INSERT INTO customers (email) VALUES ('noname@example.com')" ; echo "exit=$?"ERROR:  null value in column "name" of relation "customers" violates not-null constraintDETAIL:  Failing row contains (5, null, noname@example.com, 0.00, {}, {}, 2026-08-27 13:59:03.134066+00).exit=1

   Expected resultThe not-null error plus a DETAIL containing the whole failing row.

   Success conditionYou can see exactly what the server tried to insert.

4. 4

   Try to set an identity column by hand

   ERROR: cannot insert a non-DEFAULT value into column "id", DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS., and HINT: Use OVERRIDING SYSTEM VALUE to override.

   The HINT is the part worth noticing. The server has told you the exact syntax that would work, so the escape hatch exists and is discoverable - but you have to ask for it explicitly, which is the whole point of ALWAYS over BY DEFAULT.

   This is the protection that stops an application inserting ids that collide with the sequence and produces duplicate-key errors weeks later, on rows nobody touched. Use OVERRIDING SYSTEM VALUE for a data migration; never in application code.

   $psql -d appdb -c "INSERT INTO customers (id, name, email) VALUES (99,'Manual','manual@example.com')" ; echo "exit=$?"ERROR:  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.exit=1

   Expected resultThe error, the DETAIL naming GENERATED ALWAYS, and the HINT.

   Success conditionYou know why manual ids are refused and how to override deliberately.

Troubleshooting

• Coming from MySQL and expecting a warning rather than an error.

  Why: MySQL without strict mode adjusts values silently. PostgreSQL never does.

  Fix:Fix the data or widen the column. There is no mode to relax.

• Sensitive values appearing in the PostgreSQL log.

  Why: The Failing row contains DETAIL is logged with the error.

  Fix:Restrict log access. log_min_error_statement can raise the threshold but loses the diagnostic value.

• ERROR: cannot insert a non-DEFAULT value during a data migration.

  Why: The column is GENERATED ALWAYS.

  Fix:INSERT INTO t (id, ...) OVERRIDING SYSTEM VALUE VALUES (...), then reset the sequence with setval so future inserts do not collide.

Official sources

• PostgreSQL 18 Documentation - Data Types
• PostgreSQL 18 Documentation - Date/Time Types
• PostgreSQL 18 Documentation - Constraints
• PostgreSQL 18 Documentation - INSERT ... ON CONFLICT
• PostgreSQL 18 Documentation - JSON Types and Functions
• PostgreSQL 18 Documentation - Arrays