- Software and digital services
- Databases
23502
PostgreSQL 23502 not_null_violation: null in required column
Evidence linked to this decision
View source: PostgreSQL 18 Appendix A: Error CodesRisk level
Medium
Proceed carefully and stop if the check is unclear.
Urgency
Medium
Quick answer
PostgreSQL 23502 (not_null_violation) means an INSERT or UPDATE tried to store a null value in a column constrained as NOT NULL. Check the Schema, Table, and Column error fields to see which attribute received null, then supply a valid value or declare a column DEFAULT.
Safety first
Stop and check this first
- Never remove a `NOT NULL` constraint or substitute dummy placeholder strings in production just to silence 23502 without verifying downstream code and foreign keys that depend on real values.
- Do not attempt to share failed `INSERT` or `UPDATE` parameter dumps with external support before redacting personal data, credentials, and secret tokens.
Safe checks you can perform
- Read the exact Schema, Table, and Column names from the Class 23 error fields
- Query information_schema.columns for is_nullable and column_default on that column
- Check whether SET CONSTRAINTS DEFERRED was expected to postpone the NOT NULL check
Leave these tasks to a qualified professional
- Do not attempt this professional task: Fix the write payload, ORM column mapping, or explicit DEFAULT expression
- Do not attempt this professional task: Scope ON DELETE SET NULL column lists or alter nullability only after a backup
What a technician must be able to justify
Technician visit
These are the steps they must carry out and show you. Tick them as they work: if steps are skipped, do not accept a part swap.
These are not instructions for opening or handling the equipment yourself. They are the checks that should support a technical conclusion.
Fix the write payload, ORM column mapping, or explicit DEFAULT expression
What they doHave your developer or DBA update the application query so it supplies a non-null value (or the `DEFAULT` keyword when a column default exists), or add a proper `DEFAULT` expression / `GENERATED ... AS IDENTITY` clause if new rows should populate the column automatically.
They must show youthe updated `information_schema.columns` output or parameterized query test inserting a row without raising 23502.
In writingthe affected `table_schema.table_name.column_name`, whether the fix changed the query payload or the column `DEFAULT`, and the test result.
Scope ON DELETE SET NULL column lists or alter nullability only after a backup
What they doIf 23502 is triggered by a composite foreign key with `ON DELETE SET NULL`, have the DBA scope the action to the nullable subset using `ON DELETE SET NULL (column_name)` or, if null truly has a valid business meaning, back up the table and alter the column in a reviewed migration.
They must show youthe decompiled constraint from `pg_get_constraintdef` and a test delete or update confirming no 23502 error occurs.
In writingthe exact DDL migration applied, backup confirmation, and verification log.
Before accepting a part or repair
Nothing has been shown yet. Do not accept a part swap based on the code alone.
Follow these steps
Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.
Read the exact Schema, Table, and Column names from the Class 23 error fields
- Who:
- You or your developer inspect the application error log or `psql` verbose output for the failed write.
- How:
- Record protocol fields `s` (Schema name), `t` (Table name), `c` (Column name), and `n` (Constraint name) from the 23502 ErrorResponse.
- Result that matters:
- For Class 23 violations, PostgreSQL populates separate object-identification fields so you do not have to guess which column rejected the row.
- If field `c` names a column that your form or API payload left blank, that confirms the request sent null or omitted a required field.
- If field `c` names an internal column your payload never touches, that points to a missing column default, an ORM mapping omission, or a cascading `SET NULL` action.
- Evidence to keep:
- Save the `s`, `t`, `c`, and `n` field values along with the timestamp and redacted statement text.
Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fields
Query information_schema.columns for is_nullable and column_default on that column
- Who:
- Your developer or DBA runs a read-only query against `information_schema.columns`.
- How:
- Execute `SELECT column_name, is_nullable, column_default, is_identity FROM information_schema.columns WHERE table_schema = '<schema>' AND table_name = '<table>';` in a read-only session.
- Result that matters:
- The `columns` view reports `is_nullable` (`NO` when known not nullable) and `column_default` (the declared default expression, or null if none exists).
- If `is_nullable` is `NO` and `column_default` is null, that confirms any `INSERT` omitting the column will default to null and fail with 23502.
- If `column_default` contains a sequence or expression (such as `nextval(...)` or `CURRENT_TIMESTAMP`) yet 23502 still fires, that confirms the statement explicitly passed `NULL` instead of omitting the column or using `DEFAULT`.
- Evidence to keep:
- Keep the `information_schema.columns` row showing `column_name`, `is_nullable`, and `column_default`.
Use a read-only `SELECT` only; never drop `NOT NULL` on primary key or mandatory columns without a reviewed schema change.
Evidence: PostgreSQL 18 information_schema columns view · PostgreSQL 18 default values
Check whether SET CONSTRAINTS DEFERRED was expected to postpone the NOT NULL check
- Who:
- Your developer reviews the transaction code to see if it relies on `SET CONSTRAINTS ALL DEFERRED` while inserting incomplete rows first.
- How:
- Inspect the transaction statements for a pattern that inserts a row with a null required column and plans to `UPDATE` it before `COMMIT`.
- Result that matters:
- The `SET CONSTRAINTS` documentation states that `NOT NULL` and `CHECK` constraints are always checked immediately when a row is inserted or modified, never deferred to commit.
- If the transaction inserts a temporary null expecting deferred evaluation, that confirms why 23502 is raised immediately during the `INSERT` statement.
- If the statement already supplies all columns up front, that rules out deferred-timing assumptions and isolates the bug to a null value in the bound parameters or a `SET NULL` referential action.
- Evidence to keep:
- Record the exact `INSERT` or `UPDATE` statement order within the transaction block.
Evidence: PostgreSQL 18 SET CONSTRAINTS · PostgreSQL 18 CREATE TABLE
Still not solved
If the error persists, stop here. See what the technician must do and show you.
Virtual technician
Does 23502 still appear after these checks?
This guided flow uses the exact code and your answers to choose a test, an expected result and the next branch.
Where this code applies
A code is meaningful only inside the right product and version context.
Primary scope
- System
- Software and digital services
- Brand
- PostgreSQL
- Product type
- Databases
Known code variants
- not_null_violation
- PostgreSQL 23502
- SQLSTATE 23502
What it means
Appendix A of the PostgreSQL documentation maps SQLSTATE 23502 to `not_null_violation` in Class 23 (Integrity Constraint Violation). A not-null constraint requires that a table column never assume the null value, and primary key columns are forced `NOT NULL` as well. PostgreSQL checks `NOT NULL` constraints immediately when a row is inserted or modified rather than at statement end or commit. When an `INSERT` omits a column with no explicit `DEFAULT` clause, its default value is null, which triggers 23502. The code alone does not prove whether the client bound an explicit null, omitted a column without a default, or cleared the column via a trigger or `ON DELETE SET NULL`.
Warnings and stop conditions
- Mediumsource verified
Never remove a `NOT NULL` constraint or substitute dummy placeholder strings in production just to silence 23502 without verifying downstream code and foreign keys that depend on real values.
Evidence: PostgreSQL constraints
- Lowsource verified
Do not attempt to share failed `INSERT` or `UPDATE` parameter dumps with external support before redacting personal data, credentials, and secret tokens.
Evidence: PostgreSQL error and notice fields · PostgreSQL bug reporting guidelines
Symptoms and causes
Probable causes · in order
- An INSERT omitted a NOT NULL column that has no explicit DEFAULT expressionCommon
Section 5.2 states that when a new row is created without specifying a value for a column, PostgreSQL fills it with its default value, and if no default value is declared explicitly, the default is the null value.
Evidence: PostgreSQL 18 default values · PostgreSQL 18 CREATE TABLE
- The client statement or ORM bound an explicit NULL into a NOT NULL or PRIMARY KEY columnCommon
Section 5.5.2 specifies that a not-null constraint forbids a column from assuming the null value, and Section 5.5.4 notes that a primary key forces its participating columns to be marked NOT NULL.
Evidence: PostgreSQL constraints
- A foreign-key ON DELETE SET NULL or ON UPDATE SET NULL action attempted to null a required columnPossible
Section 5.5.5 explains that SET NULL sets referencing columns to nulls when a referenced row is deleted or updated, which fails if a target column is still required by a NOT NULL or PRIMARY KEY constraint.
Evidence: PostgreSQL constraints · PostgreSQL 18 CREATE TABLE
Sources and technical references
- 1
PostgreSQL 18 Appendix A: Error Codes
PostgreSQL Global Development Group · 2026 · official_doc
- 2
PostgreSQL Global Development Group · 2026 · official_doc
Was this entry useful?