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.
coalescereturns 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,nvl2etc with respect to databases like Oracle. Postgres does not support them.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%%sql
SELECT 1 + NULL AS result
1 rows affected.
Out[3]:
| result |
|---|
| None |
In [4]:
%%sql
SELECT coalesce(1, 0) AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[4]:
| result |
|---|
| 1 |
In [5]:
%%sql
SELECT coalesce(NULL, NULL, 2, NULL, 3) AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[5]:
| result |
|---|
| 2 |
In [6]:
%sql DROP TABLE IF EXISTS sales
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[6]:
[]
In [7]:
%%sql
CREATE TABLE IF NOT EXISTS sales(
sales_person_id INT,
sales_amount FLOAT,
commission_pct INT
)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[7]:
[]
In [8]:
%%sql
INSERT INTO sales VALUES
(1, 1000, 10),
(2, 1500, 8),
(3, 500, NULL),
(4, 800, 5),
(5, 250, NULL)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[8]:
[]
In [9]:
%%sql
SELECT * FROM sales
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[9]:
| 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 |
In [10]:
%%sql
SELECT s.*,
round((sales_amount * commission_pct / 100)::numeric, 2) AS incorrect_commission_amount
FROM sales AS s
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[10]:
| 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 |
In [11]:
%%sql
SELECT s.*,
coalesce(commission_pct, 0) AS commission_pct
FROM sales AS s
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[11]:
| 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 |
In [12]:
%%sql
SELECT s.*,
round((sales_amount * coalesce(commission_pct, 0) / 100)::numeric, 2) AS commission_amount
FROM sales AS s
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[12]:
| 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 |
In [13]:
%%sql
SELECT nullif(1, 0)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[13]:
| nullif |
|---|
| 1 |
In [14]:
%%sql
SELECT nullif(1, 1)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[14]:
| nullif |
|---|
| None |