Kenhuang Academy
  • English

      • 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