How to Add a Not Null Constraint in PostgreSQL

A NOT NULL constraint in PostgreSQL ensures that a column cannot have NULL values, which helps maintain data integrity.

Why Use a NOT NULL Constraint?

By adding a NOT NULL constraint, you guarantee that every row in your table has a value for the specified column. This is useful when you want to ensure essential data, like user email addresses or order numbers, is always present.

Adding a NOT NULL Constraint to a New Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Here, the email column is set to NOT NULL when creating the table.

Adding a NOT NULL Constraint to an Existing Column

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

This command updates the existing email column to require non-NULL values.

Important Considerations

• Make sure no existing rows have NULL values; otherwise, the command will fail.
• To check for NULLs, run:

  SELECT * FROM users WHERE email IS NULL;

• If needed, update NULL values before applying the constraint:

  UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

Removing a NOT NULL Constraint

ALTER TABLE users
ALTER COLUMN email DROP NOT NULL;

This command allows the column to accept NULL values again.

Summary

Adding a NOT NULL constraint is a simple but powerful way to improve your database’s reliability. By ensuring essential fields are never empty, you reduce the chance of errors in your application.