{"id":1786,"date":"2025-01-22T11:42:29","date_gmt":"2025-01-22T04:42:29","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1786"},"modified":"2025-01-22T11:56:42","modified_gmt":"2025-01-22T04:56:42","slug":"postgresql-is-null","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-is-null\/","title":{"rendered":"PostgreSQL IS NULL Operator"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn to use the PostgreSQL IS NULL<\/code> operator to determine if a value is NULL<\/code>.<\/p>\n\n\n\n

Introduction to the PostgreSQL IS NULL operator. #<\/a><\/h2>\n\n\n\n

In PostgreSQL, NULL<\/a><\/code> is a marker that represents unknown data. To check if a value is NULL<\/code>, you cannot compare it with NULL<\/code> using the equal to operator.<\/p>\n\n\n\n

Since NULL<\/code> is unknown, comparing a value with an unknown value will result in an unknown value (or NULL<\/code>). For example:<\/p>\n\n\n

SELECT<\/span>\n  10<\/span> = NULL<\/span> AS<\/span> result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we compare the number 10<\/code> with NULL<\/code> using the equal to operator. The comparison returns NULL<\/code>.<\/p>\n\n\n\n
To compare a value with NULL<\/code>, you use the IS NULL<\/code> operator:<\/p>\n\n\n
value<\/span> IS<\/span> NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IS NULL<\/code> operator compares the value with NULL<\/code> and returns true<\/code> if the value is NULL<\/code> or false<\/code> otherwise. For example:<\/p>\n\n\n
SELECT<\/span>\n  NULL<\/span> IS<\/span> NULL<\/span> null_vs_null,\n  10<\/span> IS<\/span> NULL<\/span> null_vs_10;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
To negate the result of the IS NULL<\/code> operator, you use the IS NOT NULL<\/code> operator.<\/p>\n\n\n
value<\/span> IS<\/span> NOT<\/span> NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IS NOT NULL<\/code> operator compares the value with NULL<\/code> and returns true<\/code> if the value is not null or false<\/code> otherwise.<\/p>\n\n\n\n
If you want to handle NULL<\/code> using functions instead of the IS NULL<\/code> operator, you can use the NULLIF<\/a>, ISNULL<\/code>, and COALESCE<\/a> functions.<\/p>\n\n\n\n
PostgreSQL IS NULL operator example #<\/a><\/h2>\n\n\n\n
We’ll use the profiles<\/code> table to demonstrate the IS NULL<\/code> operator:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following SELECT<\/a><\/code> statement uses the IS NULL<\/code> operator to retrieve users who do not have work phones:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  work_phone\nFROM<\/span>\n  profiles\nWHERE<\/span>\n  work_phone IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name | work_phone\n------------+-----------+------------<\/span>\n Alice      | Jones     | NULL<\/span>\n Bob        | Brown     | NULL<\/span>\n Charlie    | Davis     | NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns the employees with the values in the work_phone<\/code> are NULL<\/code>.<\/p>\n\n\n\n
PostgreSQL IS NOT NULL example #<\/a><\/h2>\n\n\n\n
The following query uses the IS NOT NULL<\/code> operator to retrieve employees who have work phones:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  work_phone\nFROM<\/span>\n  profiles\nWHERE<\/span>\n  work_phone IS<\/span> NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name |  work_phone\n------------+-----------+--------------<\/span>\n John       | Doe       | 408<\/span>-456<\/span>-7890<\/span>\n Jane       | Smith     | 408<\/span>-456<\/span>-7891<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
psql and NULL #<\/a><\/h2>\n\n\n\n
psql<\/code> is a client program for interacting with PostgreSQL Server using a command-line interface.<\/p>\n\n\n\n
The psql program displays blank for NULL<\/code> by default. To use the NULL<\/code> literal string to represent NULL<\/code>, you can use the following command: <\/p>\n\n\n
\\pset null<\/span> NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To ensure a column that does not have NULL<\/code>, you use the NOT NULL constraint<\/a>.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n