NOT NULL Constraint in PostgreSQL

In PostgreSQL, NULL represents an unknown or missing value — it is not the same as an empty string or zero. By default, every column accepts NULL. The NOT NULL constraint changes this: any attempt to insert or update a row with a NULL in a NOT NULL column is rejected with an error. Defining which columns require a value is one of the most fundamental aspects of schema design.

Syntax

Declare NOT NULL inline when creating a table:

CREATE TABLE table_name (
  column_name data_type NOT NULL
);

Add NOT NULL to an existing column:

ALTER TABLE table_name
  ALTER COLUMN column_name SET NOT NULL;

Remove the constraint:

ALTER TABLE table_name
  ALTER COLUMN column_name DROP NOT NULL;

Practical Example

Create an orders table with required and optional fields:

CREATE TABLE venues (
  venue_id   SERIAL PRIMARY KEY,
  venue_name VARCHAR(200) NOT NULL,
  city       VARCHAR(100) NOT NULL,
  capacity   INT
  -- capacity allows NULL (venue capacity may be unknown)
);

CREATE TABLE events (
  event_id    SERIAL PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  venue_id    INT NOT NULL REFERENCES venues (venue_id),
  starts_at   TIMESTAMPTZ NOT NULL,
  ends_at     TIMESTAMPTZ,            -- optional
  ticket_price NUMERIC(10, 2) NOT NULL DEFAULT 0.00
);

Attempting to insert without the required title:

INSERT INTO events (venue_id, starts_at)
VALUES (1, '2026-06-15 19:00:00+00');

-- ERROR:  null value in column "title" of relation "events"
--         violates not-null constraint
-- DETAIL:  Failing row contains (1, null, 1, 2026-06-15 19:00:00+00, null, 0.00).

Add NOT NULL to an existing nullable column by first cleaning up any NULL values:

-- Check for NULLs first
SELECT COUNT(*) FROM events WHERE ends_at IS NULL;

-- Backfill a reasonable default
UPDATE events
  SET ends_at = starts_at + INTERVAL '2 hours'
WHERE ends_at IS NULL;

-- Now add the constraint
ALTER TABLE events
  ALTER COLUMN ends_at SET NOT NULL;

Set NOT NULL on multiple columns in a single statement:

ALTER TABLE venues
  ALTER COLUMN capacity SET NOT NULL,
  ALTER COLUMN city     SET NOT NULL;

Verify constraints:

SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'events'
ORDER BY ordinal_position;

Conditional NOT NULL with CHECK

When business rules require “either column A or column B must be present (but not necessarily both)”, a CHECK constraint is more expressive than plain NOT NULL:

CREATE TABLE event_contacts (
  contact_id   SERIAL PRIMARY KEY,
  event_id     INT NOT NULL REFERENCES events (event_id),
  phone        VARCHAR(30),
  email        VARCHAR(100),
  CONSTRAINT chk_phone_or_email
    CHECK (
      NOT (
        (phone IS NULL OR phone = '')
        AND
        (email IS NULL OR email = '')
      )
    )
);

This allows a row with only a phone, only an email, or both — but rejects a row where both are absent. This pattern cannot be expressed with simple NOT NULL constraints on individual columns.

Testing with Vela

Adding NOT NULL to a column in a large production table requires a full table scan for validation. Before running this migration in production, use Vela’s database branching to test it against a copy-on-write clone of your real data: identify which rows need a backfill, measure the scan time, and confirm the ALTER TABLE completes without timeout errors — all without any risk to the live database.

Production Tips

• Before adding NOT NULL to an existing column, always run SELECT COUNT(*) FROM t WHERE col IS NULL; to find any rows that would fail validation.
• Adding NOT NULL to an existing column acquires an ACCESS EXCLUSIVE lock for a full table scan. On PostgreSQL 12+, adding a CHECK (col IS NOT NULL) NOT VALID constraint first, and then validating it separately, acquires a weaker lock for the validation step — though a true NOT NULL constraint still requires a separate ALTER COLUMN SET NOT NULL.
• Never use = NULL in queries — this always returns NULL, not TRUE. Use IS NULL and IS NOT NULL instead.
• A column declared NULL explicitly accepts null values. A column with neither NULL nor NOT NULL in its definition also defaults to accepting NULL.
• When a column has a DEFAULT value and is NOT NULL, an INSERT that omits the column will use the default rather than raising an error. Pair NOT NULL with a meaningful DEFAULT to avoid forced explicit values on every insert.
• Use information_schema.columns or \d table_name to audit which columns in a table allow NULL and confirm constraint state after migrations.