How to diagnose and fix the 22004 null_value_not_allowed error code in Postgres.

by

The 22004 error code in PostgreSQL, described as null_value_not_allowed, occurs when an attempt is made to insert or update a record with a NULL value in a column that does not accept NULL values, such as one that has been defined as NOT NULL (PostgreSQL 22004 – Metis Data).

To diagnose and fix this error, you should:

  1. Check the error message for details about which column is causing the issue. PostgreSQL will typically indicate the column name and the table in which the NULL value is not allowed.
  2. Review the table schema to confirm that the column is indeed set to NOT NULL.
  3. Modify the SQL statement to provide a non-NULL value for the column, or alter the table schema to allow NULL values if that is appropriate for your application.

Here are some examples and sample code to illustrate the process:

Example 1: Attempting to insert a NULL value into a NOT NULL column

Suppose you have a table employees with a column email defined as NOT NULL.

Incorrect insert statement:

INSERT INTO employees (name, email) VALUES ('John Doe', NULL);

To fix this, you should provide a valid email address:

Correct insert statement:

INSERT INTO employees (name, email) VALUES ('John Doe', 'john.doe@example.com');

Example 2: Updating a record to set a NOT NULL column to NULL

If you attempt to update a record and set a NOT NULL column to NULL, you’ll encounter the 22004 error.

Incorrect update statement:

UPDATE employees SET email = NULL WHERE name = 'Jane Smith';

To fix this, you should ensure that the email column is given a non-NULL value:

Correct update statement:

UPDATE employees SET email = 'jane.smith@example.com' WHERE name = 'Jane Smith';

Example 3: Altering table schema to allow NULL values

If your application logic has changed and you now need to allow NULL values in a column that was previously set to NOT NULL, you can alter the table schema:

ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;

After running this command, the employees table will allow NULL values in the email column.

In all cases, it’s important to ensure that your application logic and database schema are in sync regarding the handling of NULL values to prevent the 22004 error code.

Leave a Comment