ISNULL

SQL Server provides an ISNULL(expression, replacement) function that returns the replacement value when the expression is null. PostgreSQL does not have this function by that name, but achieves the same result through IS NULL / IS NOT NULL predicates and the COALESCE() function.

Testing for NULL: IS NULL and IS NOT NULL

Use IS NULL in WHERE clauses to filter rows where a column has no value. Do not use = NULL—NULL is not equal to anything, including itself.

-- Find customers without a phone number
SELECT id, name
FROM customers
WHERE phone IS NULL;

-- Find orders that have been shipped
SELECT id, placed_at, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL;

Replacing NULL with a default: COALESCE

COALESCE(expression, replacement) is PostgreSQL's equivalent of SQL Server's ISNULL. It returns the first non-null argument:

-- Equivalent to SQL Server: ISNULL(phone, 'N/A')
SELECT
  id,
  name,
  COALESCE(phone, 'N/A') AS phone
FROM customers;

Output:

 id |    name     |    phone
----+-------------+-------------
  1 | Alice Smith | 555-100-200
  2 | Bob Jones   | N/A

Using CASE for null replacement

A CASE expression provides the same null-replacement capability with explicit syntax:

SELECT
  id,
  CASE WHEN phone IS NULL THEN 'N/A' ELSE phone END AS phone
FROM customers;

COALESCE is preferred when the replacement is a fixed default because it is shorter. Use CASE when the replacement logic is more complex.

Practical tips

• Always use IS NULL or IS NOT NULL to compare with null—never = NULL or != NULL.
• COALESCE accepts multiple arguments: it scans left to right and returns the first non-null value, making it suitable for multi-level fallback chains.
• Indexes on nullable columns can still be used by IS NULL and IS NOT NULL predicates in PostgreSQL.
• Functions like COUNT(column) skip null values automatically—use COUNT(*) to count all rows including those with nulls.