PostgreSQL - NULL Values

PostgreSQL - Handling NULL Values

In SQL, NULL represents a missing, unknown, or not-applicable value. It is a state, not a value. A NULL in a table field appears blank, signifying that no data has been entered.

It is crucial to understand that a NULL value is distinct from a zero (0) or a field containing an empty string (”). Comparing a NULL value with any other value (including another NULL) using standard comparison operators like =, <, or > results in ‘unknown’, not true or false. This is a core concept of three-valued logic (3VL) in SQL.

Defining Columns that Allow NULL

When creating a table, columns allow NULL values by default. You must explicitly use the NOT NULL constraint to prevent NULLs. In the example below, the address and salary columns can be NULL.

CREATE TABLE employees (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    full_name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age > 18),
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

• NOT NULL: This constraint ensures a column must always have a value.
• Default Behavior: Columns without the NOT NULL constraint can hold NULL values.
• GENERATED … AS IDENTITY: A modern, SQL-standard way to create auto-incrementing primary keys (available since PostgreSQL 10).

Querying for NULL Values

Because column = NULL will not work as expected, SQL provides the IS NULL and IS NOT NULL operators to specifically test for the presence or absence of NULLs. Let’s use an example employees table:

id	full_name	age	address	salary
1	Alice Smith	32	123 Maple St	75000.00
2	Bob Johnson	25	456 Oak Ave	60000.00
3	Charlie Brown	29	789 Pine Ln	null
4	Diana Prince	35	null	120000.00

To find all employees whose salary is not recorded, we use IS NULL:

SELECT id, full_name, age
FROM employees
WHERE salary IS NULL;

This query will return Charlie Brown’s record:

id	full_name	age
3	Charlie Brown	29

To find all employees with a known address, we use IS NOT NULL:

SELECT id, full_name, address
FROM employees
WHERE address IS NOT NULL;

This query will return the records for Alice, Bob, and Charlie.

Functions for Handling NULL

PostgreSQL provides useful functions to handle NULLs within queries, allowing you to substitute them with other values.

The COALESCE Function

The COALESCE function returns the first of its arguments that is not NULL. It’s perfect for providing a default value for a column that might be NULL.

-- Display 'Salary Not Provided' for employees with a NULL salary.
SELECT full_name, COALESCE(salary::text, 'Salary Not Provided') AS salary_status
FROM employees;

Result:

full_name	salary_status
Alice Smith	75000.00
Bob Johnson	60000.00
Charlie Brown	Salary Not Provided
Diana Prince	120000.00

The NULLIF Function

The NULLIF(value1, value2) function returns NULL if value1 equals value2; otherwise, it returns value1. This is useful for data cleaning, such as converting an empty string to a NULL.

-- Suppose a column 'department' might contain an empty string '' instead of a proper NULL.
-- We can convert it to NULL during a select.
SELECT full_name, NULLIF(department, '') AS cleaned_department
FROM employees;

Common Errors and Best Practices

• Never use column = NULL or column != NULL. These expressions always evaluate to ‘unknown’ (which behaves like false in a WHERE clause). Always use IS NULL and IS NOT NULL.
• Be aware of aggregate functions. Functions like COUNT(column), SUM(column), and AVG(column) ignore NULL values in their calculations. COUNT(*) counts all rows regardless of NULLs.
• Use NOT NULL constraints where applicable. If a piece of information is essential for a record to be valid (like a user’s email), enforce it at the database level with a NOT NULL constraint.