Constraints Required values NOT NULL · SET NOT NULL · DEFAULT

NOT NULL Constraint

Mark a column as required. NULL gets rejected, empty strings still slip through, and pairing with DEFAULT gives you required-but-auto-filled columns. Backfill before enforcing on existing tables.

What NOT NULL does

The NOT NULL constraint marks a column as required. Every row inserted or updated must supply a non-NULL value for that column; attempts to write NULL are rejected with an error.

It's the simplest constraint and one of the most useful — fields like email, created_at, customer_id, or amount almost never make sense as NULL, and saying so up front prevents whole categories of bugs.

Syntax — at table creation

CREATE TABLE table_name (
column1 data_type NOT NULL,
column2 data_type NOT NULL
);

Example 1 — declare on creation

SQL

CREATE TABLE employees (
    employee_id    SERIAL PRIMARY KEY,
    employee_name  VARCHAR(100) NOT NULL,
    department_id  INT          NOT NULL
);

-- This succeeds
INSERT INTO employees (employee_name, department_id) VALUES ('John', 80);

-- This fails — department_id is NULL
INSERT INTO employees (employee_name, department_id) VALUES ('Jane', NULL);
-- ERROR: null value in column "department_id" of relation "employees"
--        violates not-null constraint

Adding NOT NULL to an existing column

Use ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. PostgreSQL scans the column first; if any existing row has NULL there, the operation is rejected.

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

SQL

-- Make 'email' required
ALTER TABLE employees
    ALTER COLUMN email SET NOT NULL;

⚠ Existing NULLs block the change. If any row already has NULL in that column, the ALTER fails. Backfill or update those rows first, then set the constraint.

Example 2 — backfill, then enforce

SQL

-- Step 1: replace existing NULLs with a sensible default
UPDATE employees SET email = 'unknown@example.com' WHERE email IS NULL;

-- Step 2: now the SET NOT NULL succeeds
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

Removing NOT NULL

SQL

ALTER TABLE employees
    ALTER COLUMN email DROP NOT NULL;

NOT NULL with DEFAULT

Pairing NOT NULL with a DEFAULT gives you the best of both worlds: the column is required, but if the caller doesn't supply a value, PostgreSQL fills in the default automatically.

SQL

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Both columns are required, but they auto-fill if you omit them
INSERT INTO orders DEFAULT VALUES;

Things to know

NOT NULL is a column-level constraint only — there's no table-level form.
It cannot be named the same way other constraints can. PostgreSQL stores it on the column, not in pg_constraint.
Empty string '' is not NULL — NOT NULL won't reject it. Add a CHECK if you need to forbid empty strings too: CHECK (col <> '').
An entire row can never be inserted with all NULLs into a NOT NULL table — but a missing column with a default still works.

Recap

NOT NULL forbids NULL in a column.
Set at creation with column data_type NOT NULL.
Add later with ALTER TABLE … ALTER COLUMN … SET NOT NULL; existing NULLs must be cleaned first.
Combine with DEFAULT for required-but-auto-filled columns.
Empty strings aren't caught — use a CHECK for that.