How to Handle NULL Values in PostgreSQL

How do I handle NULL values in PostgreSQL?

Use IS [NOT] NULL, COALESCE, NULLIF and related functions to detect, replace, or compare NULL values.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Handling NULL Values in PostgreSQL

PostgreSQL treats NULL as "unknown." Learn how to test, replace, and compare NULLs using built-in operators and functions.

What is a NULL value in PostgreSQL?

NULL represents an unknown or missing value. It is not zero or an empty string and never equals anything—not even another NULL.

How do I test for NULL?

Use the IS NULL and IS NOT NULL predicates. Avoid using = NULL because it always returns false.

SELECT id FROM orders WHERE shipped_at IS NULL;

How can I replace NULLs with a default?

COALESCE returns the first non-NULL argument. It is the most common way to provide defaults.

SELECT COALESCE(discount, 0) AS discount FROM invoices;

How do I compare two expressions that might be NULL?

Use NULLIF to convert identical values to NULL and IS DISTINCT FROM for safe comparisons.

-- Avoid division by zero SELECT amount / NULLIF(quantity, 0) AS unit_price FROM sales; -- Safe equality test SELECT * FROM a FULL JOIN b ON a.key IS NOT DISTINCT FROM b.key;

Which aggregate functions ignore NULL?

COUNT, SUM, AVG, MIN, and MAX automatically ignore NULLs. Use COUNT(*) to include NULL rows or FILTER for precise control.

SELECT COUNT(*) AS total_rows, COUNT(price) AS priced_rows, AVG(price) FILTER (WHERE price IS NOT NULL) AS avg_price FROM products;

When should I use IS DISTINCT FROM?

Use it in JOINs or comparisons where NULL = NULL should evaluate to true, unlike standard equality.

Best practices for NULL handling

1. Store NULL only when data is truly unknown.
2. Use COALESCE in SELECT, not in WHERE, to avoid hiding NULL logic.
3. Prefer IS DISTINCT FROM over COALESCE for comparisons to retain type safety.

Common pitfalls to avoid

1. Testing with = NULL—always false.
2. Mixing data types in COALESCE, which forces unwanted casts.

Why How to Handle NULL Values in PostgreSQL is important

How to Handle NULL Values in PostgreSQL Example Usage


SELECT id,
       COALESCE(last_login, DATE '1970-01-01') AS last_login,
       status IS NULL                        AS needs_status
FROM users;

How to Handle NULL Values in PostgreSQL Syntax


-- Detect NULL
expression IS NULL | IS NOT NULL

-- Replace NULL
COALESCE(value1 [, value2 ...])

-- Return NULL when arguments match
NULLIF(value1, value2)

-- Compare with NULL-safe semantics
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

Common Mistakes

Writing "WHERE value = NULL". Because NULL is never equal to anything, the predicate returns false for every row. Use "value IS NULL" instead.
Mixing data types in COALESCE like COALESCE(numeric_column, 'N/A'). PostgreSQL will try to cast all arguments to a common type, causing runtime errors. Provide arguments of the same type or cast explicitly.