intro
Let’s learn everything you need to know about the PostgreSQL ISNULL equivalent approaches for dealing with NULL values.
If you’re used to databases that uses ISNULL or similar (e.g. SQL Server and others), you might search for “PostgreSQL ISNULL” to figure out how to deal with NULLs in Postgres. The key is: PostgreSQL does not have an ISNULL function. Instead, it relies on the standard SQL COALESCE function together with CASE and NULLIF to handle NULL values.
Let’s learn more about those alternatives!
Checking for NULL in PostgreSQL
Before digging into how to deal with NULLs in Postgres, remember that SQL uses the IS NULL or IS NOT NULL syntax to check if a value is NULL or not in a condition. For example:
1
SELECT * FROM users
2
WHERE email IS NULL;
The above query returns all users with no email.
Note: In SQL, NULL is not “equal” to anything, not even itself, so email = NULL will not work. Thus, you must use IS NULL.
Conversely, IS NOT NULL helps you finds rows where email is present:
1
SELECT * FROM users
2
WHERE email IS NOT NULL;
Now, on to replacing or dealing with NULL in a PostgreSQL result set!
Replacing NULL with a Default Value (COALESCE and CASE)
To replace a NULL with a given “replacement” string, in SQL Server you would end up writing:
1
ISNULL(col, 'replacement')
For this scenario, the PostgreSQL ISNULL equivalent is:
1
COALESCE(col, 'replacement')
The PostgreSQLCOALESCE(value, fallback) function returns the first non-NULL argument. Consider this example:
1
SELECT COALESCE(address, 'N/A') AS address_display
2
FROM customers;
The will output the customer’s address, or 'N/A' if the address is NULL. Verify that by executing the query in a visual PostgreSQL client like DbVisualizer:
That is effectively the same logic as ISNULL in T-SQL. Note that the SQL COALESCE function accepts multiple values if needed (it will return the first that is not NULL among the list).
So, for replacing NULLs in PostgreSQL, use COALESCE. COALESCE is part of standard SQL and works across many DBs (SQL Server, MySQL and Oracle also support COALESCE). That is a straightforward substitute for ISNULL in Postgres:
1
-- SQL Server style (not valid in Postgres):
2
-- SELECT ISNULL(price, 0) FROM products;
3
4
-- PostgreSQL style:
5
SELECT COALESCE(price, 0) FROM products;
If price is NULL, COALESCE returns 0. Otherwise, it will return the actual price.
Note: COALESCE is very efficient and is effectively inlined by the optimizer.
PostgreSQL also supports the CASE expression which can do the same thing:
1
SELECT
2
CASE
3
WHEN price IS NULL THEN 0
4
ELSE price
5
END as price_value
6
FROM products;
At the same time, this ISNULL PostgreSQL equivalent approach is more verbose. In a way, COALESCE is basically a shorthand for that pattern.
Does PostgreSQL Have Any ISNULL at All?
Other ISNULL Related Functions
Example of a NULL Handling Use Case in PostgreSQL
Imagine a query that concatenates a user's first name and nickname. If nickname is null, you want just the first name. In Postgres, you can achieve that with:
1
SELECT first_name || ' "' || COALESCE(nickname, '') || '"' || last_name
2
FROM users;
Here, if nickname is NULL, COALESCE returns empty string '', so you end up with first_name "" last_name. You might adjust formatting to avoid double quotes if nickname is missing (maybe conditionally include them).
Let’s consider another example. This time, you want to sum up values where some may be NULL:
1
SELECT COALESCE(price, 0) + COALESCE(tax, 0) as total_cost
2
FROM purchases;
This ensures if price or tax is NULL, treat it as 0 in the addition operation.
Summary
By using COALESCE, CASE expressions, or NULLIF, you can effectively handle NULL values in PostgreSQL. Those methods lead to the same outcomes as ISNULL in other systems.
As shown here, dealing with NULL values in PostgreSQL becomes easier with a visual database client like DbVisualizer. This is a fully-featured database client that supports multiple DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-type schemas with a few clicks. Try it for free, and until next time.
