PostgreSQL NOT NULL Constraint

In PostgreSQL, a NOT NULL constraint prevents a column from storing the SQL NULL value. Use it when a value is required for every row.

PostgreSQL Error 23502: NOT NULL violation

SQLSTATE 23502 (not_null_violation) means PostgreSQL found a NULL where a NOT NULL rule requires a value. It commonly follows an INSERT or UPDATE, and can also occur when PostgreSQL checks existing rows while adding the constraint. The error message identifies the affected column. PostgreSQL lists this condition in its error-code appendix.

NULL means a value is missing or unknown. It is different from an empty string (''), zero, or false. A text column with NOT NULL can still contain an empty string. If empty or whitespace-only strings are also invalid, add a separate CHECK constraint.

Define a NOT NULL column

Add NOT NULL after the column’s data type in CREATE TABLE:

CREATE TABLE user_hobby (
    hobby_id serial PRIMARY KEY,
    user_id integer NOT NULL,
    hobby text NOT NULL
);

Both user_id and hobby must have non-NULL values. This insert succeeds:

INSERT INTO user_hobby (user_id, hobby)
VALUES (1, 'Reading');

An explicit NULL value is rejected:

INSERT INTO user_hobby (user_id, hobby)
VALUES (2, NULL);

This raises an error similar to:

ERROR: null value in column "hobby" of relation "user_hobby" violates not-null constraint

The associated SQLSTATE is 23502.

The same rule applies when an insert omits a NOT NULL column that has no default. A column default is used when the column is omitted, but an explicit NULL still violates NOT NULL.

Distinguish NULL from an empty string

An empty string is a valid text value, so this insert succeeds even though hobby is NOT NULL. Use a transaction and roll it back to try the insert without keeping the sample row:

BEGIN;
INSERT INTO user_hobby (user_id, hobby)
VALUES (2, '');
ROLLBACK;

To reject empty and whitespace-only hobbies as well, add a check. Existing blank values must be handled first or PostgreSQL will reject the constraint. See our PostgreSQL CHECK constraints guide for more examples:

ALTER TABLE user_hobby
ADD CONSTRAINT user_hobby_hobby_not_blank
CHECK (btrim(hobby) <> '');

The NOT NULL constraint requires a value; the CHECK expression enforces the additional rule that the text must contain a non-space character. PostgreSQL also treats NULL differently from an empty string when you check for NULL values.

Add NOT NULL to an existing column

Before adding NOT NULL, find and resolve rows where the column is currently NULL. For example, count them first:

SELECT COUNT(*) AS null_hobby_count
FROM user_hobby
WHERE hobby IS NULL;

If the value is missing, choose a replacement that is valid for your application. For example, use Unspecified only if that is a meaningful category in your data:

UPDATE user_hobby
SET hobby = 'Unspecified'
WHERE hobby IS NULL;

After the query returns no matching rows, add the constraint:

ALTER TABLE user_hobby
ALTER COLUMN hobby SET NOT NULL;

PostgreSQL checks existing rows when applying this change. If any NULL values remain, the command fails and the constraint is not added. For more details about changing a table, see PostgreSQL’s ALTER TABLE documentation.

Set a default value

A default can fill a column when an insert omits it. Set a default with ALTER TABLE:

ALTER TABLE user_hobby
ALTER COLUMN hobby SET DEFAULT 'Unspecified';

An insert that leaves out hobby now uses the default:

INSERT INTO user_hobby (user_id)
VALUES (3);

The default does not replace an explicit NULL. An insert that supplies NULL for hobby still fails because of the NOT NULL constraint.

Remove a NOT NULL constraint

If a column should be allowed to store NULL values again, drop its NOT NULL constraint:

ALTER TABLE user_hobby
ALTER COLUMN hobby DROP NOT NULL;

The command changes the column rule; it does not change existing rows.

Summary

Use NOT NULL to require a value, and use a separate CHECK constraint when values such as empty strings must also be rejected. When adding NOT NULL to an existing column, find and handle its NULL rows first. See PostgreSQL’s constraints documentation for more details, or browse the PostgreSQL error troubleshooting index for other SQLSTATE guides.