PostgreSQL IS NULL Operator

In SQL, NULL represents missing or unknown information. It is not a value — it is the absence of a value. PostgreSQL provides the IS NULL and IS NOT NULL operators specifically for testing whether a value is absent, because standard equality operators (=, <>) do not work with NULL.

Syntax

-- Test for NULL
value IS NULL

-- Test for a concrete (non-NULL) value
value IS NOT NULL

Why = NULL Does Not Work

Any comparison involving NULL evaluates to NULL (not TRUE or FALSE):

SELECT NULL = NULL AS result;
-- result: null

SELECT NULL <> NULL AS result;
-- result: null

SELECT NULL > 5 AS result;
-- result: null

The WHERE clause treats NULL as FALSE, so WHERE column = NULL never returns any rows. Always use IS NULL:

-- This never returns rows
SELECT * FROM invoices WHERE cancelled_at = NULL;  -- wrong

-- This correctly finds cancelled invoices
SELECT * FROM invoices WHERE cancelled_at IS NULL; -- correct

Practical Example

Create a sample table for a service ticket system where some fields are optional:

CREATE TABLE tickets (
  ticket_id    SERIAL PRIMARY KEY,
  subject      VARCHAR(200) NOT NULL,
  assigned_to  VARCHAR(80),          -- NULL means unassigned
  resolved_at  TIMESTAMPTZ,          -- NULL means still open
  priority     VARCHAR(10)  NOT NULL DEFAULT 'medium',
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

INSERT INTO tickets (subject, assigned_to, resolved_at, priority) VALUES
  ('Login page 500 error',     'alice@example',  '2026-03-10 14:00+00', 'high'),
  ('Slow dashboard queries',   'bob@example',    NULL,                  'high'),
  ('Export CSV broken',        NULL,                 NULL,                  'medium'),
  ('Password reset email delay',NULL,                NULL,                  'low'),
  ('Dark mode flicker',        'alice@example',  '2026-03-22 09:00+00', 'low'),
  ('API rate limit docs',      'carol@example',  NULL,                  'medium');

Find all unassigned open tickets:

SELECT ticket_id, subject, priority
FROM tickets
WHERE assigned_to IS NULL
  AND resolved_at IS NULL
ORDER BY priority;

Expected result:

 ticket_id |           subject            | priority
-----------+------------------------------+----------
         3 | Export CSV broken            | medium
         4 | Password reset email delay   | low

Find all tickets that have been resolved (resolved_at is not NULL):

SELECT ticket_id, subject, assigned_to, resolved_at
FROM tickets
WHERE resolved_at IS NOT NULL
ORDER BY resolved_at;

Inspect the column nullability in the schema:

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

NULL in Aggregate Functions

Aggregate functions automatically skip NULL values, which can cause subtle discrepancies:

SELECT
  COUNT(*)                AS total_tickets,
  COUNT(assigned_to)      AS assigned_count,
  COUNT(resolved_at)      AS resolved_count
FROM tickets;

Result:

 total_tickets | assigned_count | resolved_count
---------------+----------------+----------------
             6 |              3 |              2

COUNT(*) counts all 6 rows, but COUNT(assigned_to) counts only the 3 non-NULL values.

COALESCE: Substituting a Default for NULL

-- Show 'Unassigned' instead of NULL
SELECT ticket_id, subject,
       COALESCE(assigned_to, 'Unassigned') AS assigned_to
FROM tickets
ORDER BY ticket_id;

NULL-Safe Equality with IS NOT DISTINCT FROM

Standard equality operators treat two NULL values as non-matching (because NULL = NULL is NULL, not TRUE). Use IS NOT DISTINCT FROM when you want NULL to equal NULL:

-- Returns TRUE even when both sides are NULL
SELECT NULL IS NOT DISTINCT FROM NULL AS result;
-- result: true

This is useful in JOIN conditions on nullable columns.

NOT IN and the NULL Trap

-- If the subquery returns any NULL, NOT IN returns zero rows
SELECT subject FROM tickets
WHERE ticket_id NOT IN (SELECT ticket_id FROM some_table);
-- Dangerous if some_table.ticket_id can be NULL

Use NOT EXISTS as a safe alternative:

SELECT t.subject
FROM tickets t
WHERE NOT EXISTS (
  SELECT 1 FROM some_table s WHERE s.ticket_id = t.ticket_id
);

Testing with Vela

NULL-handling bugs are notoriously easy to introduce and hard to catch in testing because they only surface when actual NULL values exist in the data. Use Vela’s database branching to run IS NULL / IS NOT NULL queries against a production-data copy, where nullable columns reflect real-world data distribution. This reveals filtering gaps that synthetic test data with all-populated columns would never expose.

Production Tips

• Never use = NULL or <> NULL in a WHERE clause — always use IS NULL or IS NOT NULL.
• Use COALESCE(column, default) to substitute a fallback value when a column may be NULL, especially in reports and aggregations.
• NOT IN with a subquery is dangerous when the subquery can return NULL. Use NOT EXISTS or add an explicit IS NOT NULL filter inside the subquery.
• PostgreSQL B-tree indexes store NULL entries, so IS NULL and IS NOT NULL can benefit from index scans on indexed columns. Verify with EXPLAIN ANALYZE.
• Use NULLS FIRST or NULLS LAST in ORDER BY to explicitly control where NULL values appear — by default, NULL sorts last in ASC order and first in DESC order.
• Document which columns are intentionally nullable in your schema comments to prevent future developers from introducing = NULL bugs.