PostgreSQL NULL Values

Overview

NULL represents "unknown" or "no value" - it is not equal to an empty string or zero. Handling NULL values correctly in PostgreSQL is crucial for data integrity and query accuracy.

NULL Characteristics

Basic Properties

• NULL is not equal to any value, including NULL itself
• Any operation with NULL results in NULL
• NULL represents "unknown" in boolean operations

-- NULL comparisons
SELECT NULL = NULL;      -- Result: NULL (not TRUE)
SELECT NULL <> NULL;     -- Result: NULL
SELECT NULL = 0;         -- Result: NULL
SELECT NULL = '';        -- Result: NULL

Detecting NULL Values

IS NULL and IS NOT NULL

-- Find null values
SELECT * FROM users WHERE email IS NULL;

-- Find non-null values
SELECT * FROM users WHERE email IS NOT NULL;

-- Wrong (never returns results)
SELECT * FROM users WHERE email = NULL;  -- Don't do this!

NULL in Operations

Arithmetic Operations

SELECT 10 + NULL;    -- Result: NULL
SELECT 10 * NULL;    -- Result: NULL
SELECT 10 / NULL;    -- Result: NULL
SELECT NULL + NULL;  -- Result: NULL

String Concatenation

SELECT 'Hello' || NULL;  -- Result: NULL
SELECT CONCAT('Hello', NULL, 'World');  -- Result: 'HelloWorld' (CONCAT ignores NULL)

Boolean Operations

SELECT TRUE AND NULL;   -- Result: NULL
SELECT FALSE AND NULL;  -- Result: FALSE
SELECT TRUE OR NULL;    -- Result: TRUE
SELECT FALSE OR NULL;   -- Result: NULL
SELECT NOT NULL;        -- Result: NULL

Functions for Handling NULL

COALESCE Function

Returns the first non-NULL value:

-- Basic usage
SELECT COALESCE(NULL, 'default');  -- Result: 'default'
SELECT COALESCE(NULL, NULL, 'third');  -- Result: 'third'
SELECT COALESCE('first', NULL, 'third');  -- Result: 'first'

-- Practical application
SELECT
    username,
    COALESCE(nickname, username) as display_name
FROM users;

-- Handle possibly NULL calculations
SELECT
    product_name,
    COALESCE(discount_price, original_price) as actual_price
FROM products;

NULLIF Function

Returns NULL if two values are equal:

-- Basic usage
SELECT NULLIF(10, 10);  -- Result: NULL
SELECT NULLIF(10, 20);  -- Result: 10

-- Avoid division by zero
SELECT 100 / NULLIF(divisor, 0) FROM calculations;

-- Convert empty string to NULL
SELECT NULLIF(email, '') FROM users;

NVL Function (via COALESCE)

-- PostgreSQL doesn't have NVL, use COALESCE instead
SELECT COALESCE(commission, 0) FROM employees;

NULL and Aggregate Functions

Aggregates Ignore NULL

-- COUNT(*) counts all rows, COUNT(column) ignores NULL
SELECT COUNT(*) FROM users;         -- All rows
SELECT COUNT(email) FROM users;     -- Rows where email is not NULL

-- SUM, AVG, etc. ignore NULL values
SELECT AVG(salary) FROM employees;  -- Only averages non-NULL values

-- Example data
-- salary: 100, 200, NULL, 300
SELECT AVG(salary);  -- Result: 200 (not 150)

Including NULL in Aggregates

-- If you need to treat NULL as 0
SELECT AVG(COALESCE(salary, 0)) FROM employees;

-- Count NULL values
SELECT COUNT(*) - COUNT(column_name) as null_count FROM table_name;

NULL and Sorting

Default Sort Behavior

-- NULLs are last when ASC
SELECT * FROM products ORDER BY price ASC;

-- NULLs are first when DESC
SELECT * FROM products ORDER BY price DESC;

Controlling NULL Sort Position

-- NULL first
SELECT * FROM products ORDER BY price NULLS FIRST;

-- NULL last
SELECT * FROM products ORDER BY price DESC NULLS LAST;

NULL and DISTINCT

-- DISTINCT treats all NULLs as equal
SELECT DISTINCT category FROM products;
-- Multiple NULLs return only one NULL

NULL and Indexes

B-tree Index

-- Default B-tree index doesn't include NULL values for some queries
CREATE INDEX idx_email ON users(email);

-- Queries for NULL may not use the index
SELECT * FROM users WHERE email IS NULL;

Partial Index Optimization

-- Create index for non-NULL values only
CREATE INDEX idx_email_not_null ON users(email)
WHERE email IS NOT NULL;

-- Create index for NULL values only
CREATE INDEX idx_email_null ON users((email IS NULL))
WHERE email IS NULL;

NULL and Constraints

NOT NULL Constraint

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- NULL not allowed
    email VARCHAR(100)              -- NULL allowed
);

UNIQUE Constraint and NULL

-- UNIQUE allows multiple NULL values
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE
);

INSERT INTO test (code) VALUES (NULL);
INSERT INTO test (code) VALUES (NULL);  -- Success! Multiple NULLs allowed

CHECK Constraint and NULL

-- NULL is considered to pass CHECK constraints
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL CHECK (price > 0)
);

INSERT INTO products (price) VALUES (NULL);  -- Success!

Practical Examples

Handling Optional Fields

SELECT
    first_name,
    last_name,
    COALESCE(middle_name, '') as middle_name,
    COALESCE(phone, 'N/A') as phone
FROM customers;

Conditional Replacement

SELECT
    product_name,
    CASE
        WHEN stock IS NULL THEN 'Unknown'
        WHEN stock = 0 THEN 'Out of Stock'
        ELSE 'In Stock'
    END as availability
FROM products;

Safe Numeric Calculations

SELECT
    order_id,
    quantity * COALESCE(unit_price, 0) as line_total
FROM order_items;

Best Practices

1. Use IS NULL: Never use = NULL for comparisons
2. Use COALESCE: Provide default values to prevent NULL propagation
3. Watch aggregates: Understand how aggregate functions handle NULL
4. Document: Record which columns allow NULL and their business meaning
5. Default values: Consider using DEFAULT instead of allowing NULL