Skip to main content

Null values

Before we dive into the topic of handling NULL values in PostgreSQL, let's understand what NULL is in SQL.

What is NULL in SQL?​

In SQL, NULL is a special marker used in SQL to indicate that a data value does not exist in the database. In other words, it represents missing or unknown data. It's important to note that NULL is not the same as zero, an empty string, or any other default value.

Handling NULL Values​

When it comes to handling NULL values in PostgreSQL, there are various functions and operators we can use.

1. IS NULL​

The IS NULL operator is used to test for NULL values. It returns true if the value is NULL.

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

2. IS NOT NULL​

The IS NOT NULL operator is the opposite of IS NULL. It tests whether a value is not NULL.

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

3. COALESCE​

COALESCE is a very useful function in PostgreSQL that returns the first non-NULL value in a list.

SELECT COALESCE(column_name, replacement_value)
FROM table_name;

If column_name is NULL, the replacement_value will be returned.

4. NULLIF​

The NULLIF function returns NULL if the two given arguments are equal.

SELECT NULLIF(expression1, expression2)
FROM table_name;

If expression1 equals expression2, NULLIF returns NULL.

5. NVL​

In some databases, the NVL function can be used to replace NULL values with a specified value.

SELECT NVL(column_name, replacement_value)
FROM table_name;

This function works in a similar way to COALESCE, but NVL is specific to Oracle SQL.

Considerations When Working With NULL​

It's important to remember that NULL values are treated differently in SQL. For instance, NULL is not equal to NULL. Also, if you concatenate a NULL with a string, the result is NULL.

Furthermore, when you perform an arithmetic operation with a NULL, the result is NULL.

SELECT NULL + 10;  -- Returns NULL

Conclusion​

Understanding NULL values in PostgreSQL is an essential part of learning SQL. Remember that NULL represents a missing or unknown data value. By using IS NULL, IS NOT NULL, COALESCE, NULLIF, and NVL, you can handle NULL values effectively in your SQL queries.