NOT NULL Constraint

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.

Declaring NOT NULL when creating a table

CREATE TABLE invoices (
  id          SERIAL PRIMARY KEY,
  product_id  INT     NOT NULL,
  qty         NUMERIC NOT NULL CHECK (qty > 0),
  net_price   NUMERIC CHECK (net_price > 0)
);

Here product_id and qty require a value; net_price allows NULL. A column can have multiple constraints (NOT NULL, CHECK, UNIQUE, FOREIGN KEY) listed in any order.

Adding NOT NULL to an existing column

If the column already contains NULL values, ALTER TABLE ... SET NOT NULL will fail. You must update NULLs to valid values first:

CREATE TABLE production_orders (
  id          SERIAL PRIMARY KEY,
  description VARCHAR(40) NOT NULL,
  material_id VARCHAR(16),
  qty         NUMERIC,
  start_date  DATE,
  finish_date DATE
);

INSERT INTO production_orders (description) VALUES ('Make for Infosys inc.');

-- Update NULLs before adding the constraint:
UPDATE production_orders SET qty = 1;

ALTER TABLE production_orders ALTER COLUMN qty SET NOT NULL;

Add NOT NULL to multiple columns in a single statement:

UPDATE production_orders
SET material_id = 'ABC',
    start_date  = '2015-09-01',
    finish_date = '2015-09-01';

ALTER TABLE production_orders
  ALTER COLUMN material_id SET NOT NULL,
  ALTER COLUMN start_date  SET NOT NULL,
  ALTER COLUMN finish_date SET NOT NULL;

An attempt to set a constrained column to NULL now raises an error:

UPDATE production_orders SET qty = NULL;
-- ERROR:  null value in column "qty" violates not-null constraint

The special case: CHECK instead of NOT NULL

When you need "either column A or column B must be non-null (but not both required)", use a CHECK constraint instead:

CREATE TABLE users (
  id       SERIAL PRIMARY KEY,
  username VARCHAR(50),
  password VARCHAR(50),
  email    VARCHAR(50),
  CONSTRAINT username_email_notnull
    CHECK (
      NOT (
        (username IS NULL OR username = '')
        AND
        (email IS NULL OR email = '')
      )
    )
);

This allows a row with only a username, only an email, or both — but rejects a row where both are absent.

Production tips

• Never use = to compare a value with NULL — it always returns NULL. Use IS NULL or IS NOT NULL instead.
• Before adding NOT NULL to an existing column, run SELECT COUNT(*) FROM t WHERE col IS NULL; to find and fix any nulls first.
• Adding NOT NULL to a column scans the entire table to validate existing rows. On large tables, consider doing this during a maintenance window.
• If you declare a column as NULL explicitly, it accepts NULL values. If you omit both NULL and NOT NULL, the column defaults to accepting NULL.