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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT 1 + NULL AS result

Copy to clipboard

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

Copy to clipboard

result
None
%%sql

SELECT coalesce(1, 0) AS result

Copy to clipboard

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

Copy to clipboard

result
1
%%sql

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

Copy to clipboard

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

Copy to clipboard

result
2
%sql DROP TABLE IF EXISTS sales

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT * FROM sales

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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)

Copy to clipboard

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

Copy to clipboard

nullif
1
%%sql

SELECT nullif(1, 1)

Copy to clipboard

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

Copy to clipboard

nullif
None