IS NULL in PostgreSQL

PostgreSQL uses IS NULL and IS NOT NULL to test whether a value is null. SQL Server’s ISNULL(expression, replacement) function—which substitutes a default when the expression is null—does not exist in PostgreSQL by that name. The equivalent is COALESCE(expression, replacement), the SQL-standard form. Understanding how NULL propagates through expressions and comparisons is essential for writing correct queries.

Syntax

Testing for null in a WHERE clause:

-- Returns rows where the column has no value
SELECT * FROM table_name WHERE column_name IS NULL;

-- Returns rows where the column has a value
SELECT * FROM table_name WHERE column_name IS NOT NULL;

Replacing null with a default value:

-- Standard SQL (PostgreSQL's preferred form)
COALESCE(expression, replacement)

-- Verbose alternative using CASE
CASE WHEN expression IS NULL THEN replacement ELSE expression END

Practical Example

An orders table where some shipments have not yet left the warehouse:

CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  shipped_at   TIMESTAMPTZ    -- NULL until the order ships
);

INSERT INTO orders (product_name, placed_at, shipped_at) VALUES
  ('Laptop Stand',  '2026-03-01 09:00:00+00', '2026-03-02 14:00:00+00'),
  ('USB Hub',       '2026-03-03 11:00:00+00', NULL),
  ('Monitor Arm',   '2026-03-04 08:30:00+00', NULL),
  ('Keyboard Tray', '2026-03-05 10:00:00+00', '2026-03-06 09:00:00+00');

-- Find orders that have not yet shipped
SELECT id, product_name, placed_at
FROM orders
WHERE shipped_at IS NULL;

 id | product_name | placed_at
----+--------------+------------------------
  2 | USB Hub      | 2026-03-03 11:00:00+00
  3 | Monitor Arm  | 2026-03-04 08:30:00+00

Display a human-readable status label with COALESCE:

SELECT
  id,
  product_name,
  placed_at,
  COALESCE(shipped_at::TEXT, 'Pending shipment') AS shipment_status
FROM orders
ORDER BY placed_at;

Anti-Join Pattern with LEFT JOIN and IS NULL

IS NULL in a WHERE clause after a LEFT JOIN is the standard way to find rows with no matching record in a related table:

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE inventory (
  product_id INTEGER REFERENCES products(id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (product_id)
);

INSERT INTO products (name) VALUES ('Widget A'), ('Widget B'), ('Widget C');
INSERT INTO inventory (product_id, quantity) VALUES (1, 50), (3, 12);

-- Products that have no inventory record
SELECT p.id, p.name
FROM products p
LEFT JOIN inventory i ON i.product_id = p.id
WHERE i.product_id IS NULL;

 id |   name
----+----------
  2 | Widget B

Inspect whether PostgreSQL uses an index for IS NULL on the shipped_at column:

EXPLAIN SELECT id FROM orders WHERE shipped_at IS NULL;

Testing with Vela

Null-handling bugs are hard to catch in staging environments that lack representative data. Use a Vela branch cloned from production to run queries with real NULL distributions—including edge cases like columns that are entirely NULL in some product categories or time periods—before deploying changes to production.

Production Tips

• Never compare to NULL with = or !=. Both always return NULL (unknown), so those conditions never match any row. Use IS NULL and IS NOT NULL exclusively.
• PostgreSQL B-tree indexes do index NULL values, so IS NULL and IS NOT NULL can use an index. Create a partial index (WHERE col IS NOT NULL) on columns that are mostly non-null and frequently queried with IS NOT NULL to make the index smaller and faster.
• COUNT(column) silently skips NULL rows. When counting rows that include nulls, use COUNT(*).
• For multi-step fallbacks, chain COALESCE arguments rather than nesting CASE expressions—it is more readable and functionally equivalent.
• When migrating from SQL Server, replace ISNULL(col, default) with COALESCE(col, default) directly—the behavior is identical for two-argument usage.