Handling NULL ValuesΒΆ

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 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

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT 1 + NULL AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
None 
%%sql

SELECT coalesce(1, 0) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
1 
%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
2 
%sql DROP TABLE IF EXISTS sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

[]

%%sql

SELECT * FROM sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct
1 1000.0 10
2 1500.0 8
3 500.0 None
4 800.0 5
5 250.0 None 
%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct incorrect_commission_amount
1 1000.0 10 100.00
2 1500.0 8 120.00
3 500.0 None None
4 800.0 5 40.00
5 250.0 None None 
%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct commission_pct_1
1 1000.0 10 10
2 1500.0 8 8
3 500.0 None 0
4 800.0 5 5
5 250.0 None 0 
%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct commission_amount
1 1000.0 10 100.00
2 1500.0 8 120.00
3 500.0 None 0.00
4 800.0 5 40.00
5 250.0 None 0.00 
%%sql

SELECT nullif(1, 0)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
nullif
1 
%%sql

SELECT nullif(1, 1)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
nullif
None
Data Type Conversion Using CASE and WHEN