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 |