Database Essentials - Predefined Functions - Handling NULL Values

Databases Postgres
1

Let us understand how to handle nulls.

  • By default if we try to add or concatenate null to another column or expression or literal, it will return null.

  • If we want to replace null with some default value, we can use coalesce.

    • Replace commission_pct with 0 if it is null.

  • coalesce returns first not null value if we pass multiple arguments to it.

  • We have a function called as nullif. If the first argument is equal to the second argument, it returns null. It is typically used when we compare against 2 columns where nulls are also involved.

  • You might have seen functions like nvl, nvl2 etc with respect to databases like Oracle. Postgres does not support them.

%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
%%sql

SELECT 1 + NULL AS result

SELECT coalesce(1, 0) AS result

SELECT coalesce(NULL, NULL, 2, NULL, 3) AS result

%sql DROP TABLE IF EXISTS sales
%%sql

CREATE TABLE IF NOT EXISTS sales(
    sales_person_id INT,
    sales_amount FLOAT,
    commission_pct INT
)

%%sql

INSERT INTO sales VALUES
    (1, 1000, 10),
    (2, 1500, 8),
    (3, 500, NULL),
    (4, 800, 5),
    (5, 250, NULL)

%%sql

SELECT * FROM sales

%%sql

SELECT s.*, 
    round((sales_amount * commission_pct / 100)::numeric, 2) AS incorrect_commission_amount
FROM sales AS s

%%sql

SELECT s.*, 
    coalesce(commission_pct, 0) AS commission_pct
FROM sales AS s

%%sql

SELECT s.*, 
    round((sales_amount * coalesce(commission_pct, 0) / 100)::numeric, 2) AS commission_amount
FROM sales AS s

%%sql

SELECT nullif(1, 0)

%%sql

SELECT nullif(1, 1)

Watch the video tutorial here