How to Add NOT NULL Constraint in PostgreSQL: Syntax and Examples

To add a NOT NULL constraint in PostgreSQL, use the ALTER TABLE statement with ALTER COLUMN and SET NOT NULL. This ensures the column cannot have NULL values.

Syntax

The syntax to add a NOT NULL constraint to an existing column is:

• ALTER TABLE table_name: specifies the table to change.
• ALTER COLUMN column_name: specifies the column to modify.
• SET NOT NULL: adds the NOT NULL constraint to the column.

sql

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

Example

This example shows how to add a NOT NULL constraint to the email column in the users table.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255)
);

-- Add NOT NULL constraint to email column
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

Output

CREATE TABLE
ALTER TABLE

Common Pitfalls

Trying to add a NOT NULL constraint to a column that already contains NULL values will cause an error. You must first update or remove NULL values before adding the constraint.

Example of error:

ERROR:  column "email" contains null values

Correct approach:

• Update NULL values to a default or valid value.
• Then add the NOT NULL constraint.

sql

UPDATE users SET email = '[e-mail]' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Output

UPDATE 0
ALTER TABLE

Quick Reference

Command	Description
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;	Add NOT NULL constraint to a column
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;	Remove NOT NULL constraint from a column
UPDATE table_name SET column_name = value WHERE column_name IS NULL;	Replace NULL values before adding NOT NULL

Key Takeaways

Use ALTER TABLE with ALTER COLUMN and SET NOT NULL to add a NOT NULL constraint.

Ensure no NULL values exist in the column before adding the constraint.

Update or remove NULL values first to avoid errors.

You can remove the NOT NULL constraint with DROP NOT NULL if needed.

Always test changes on a development database before applying to production.