PostgreSQL - Day 5

• Fri, Jun 10, 2022
• One-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

Today we’ll learn ‘ALIAS’, ‘COALESCE’ and ‘NULLIF’.

30. Alias

SQL aliases are used to give a table, or a column in a table, a temporary name.

We can use ALIAS to provide a column name for the result of an operation.

• The ROUND can’t deliver what the column stands for.

SELECT make, price, ROUND(price * 0.3), ROUND(price - (price * 0.3)) FROM car;

• Use ALIAS to give it a meaningful name

SELECT make, price,
ROUND(price * 0.3) AS discount,
ROUND(price - (price * 0.3)) AS thirty_percent_off 
FROM car;

31. Coalesce

Learn how to handle null with Postgres

The COALESCE returns the first of its arguments that is not null.

• Set a default value (Email not provided) for null email

SELECT COALESCE(email, 'Email not provided') FROM person;

32. NULLIF

The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.

We can use it to check if we divide a value by 0.

SELECT 10 / NULLIF(0, 0);

If a value is divided by zero, the result is null.

Furthermore, we can combine it with COALESCE to give a default value of 0.

SELECT COALESCE(10 / NULLIF(0, 0), 0);