Introduction to PostgreSQL COALESCE
PostgreSQL Coalesce function works same as IFNULL function in SQL, it is an built in function of PostgreSQL. This function will evaluate values or arguments from left to right for finding the first non-null value or argument, after finding first argument it will not evaluate the remaining argument. Coalesce function will accept a number of arguments or values but it returns first argument or values as a result which is non-null, if all the values are null then PostgreSQL coalesce function will return the empty result or null value in result set.
Syntax:
Below given is the syntax as follows:
COALESCE (Value (Any value which we have used with coalesce function) [, ….])
COALESCE (Argument1, Argument2,….,ArgumentN);
Select column1, column2,…., columnN
COALESCE(description, short_description, '(none)')
From table_name;
Parameters:
1. Value: Any value which we have used with coalesce function in PostgreSQL. For multiple values coalesce function will return a first non-null value in result.
2. Coalesce: Coalesce states that function name in PostgreSQL which returns as first non-null value as a result. Coalesce function is very important and useful in PostgreSQL.
3. Select: Select is used to fetch data from table by using a coalesce function in PostgreSQL. We can use multiple column or single column at one time to fetch data from table.
4. Argument 1 to Argument N: Argument is nothing but an integer value that we have passing with coalesce function in PostgreSQL. If we are passing three argument and first contains null value then coalesce function will return second value in a result.
5. Column 1 to Column N: This is a column name of table. If we want to fetch data from table using coalesce function in PostgreSQL then we pass a multiple column at one time. Also we have given column name with coalesce function in PostgreSQL.
6. From: From is keyword in PostgreSQL used with table name in select query.
7. Table name: Table name is used with coalesce function.
8. Description: We are passing description with coalesce function in PostgreSQL. In description of coalesce function we have also pass a column name of a table on which we have using a coalesce function.
9. Shot description: We have also used short description parameter while using coalesce function in PostgreSQL.
10. None: It is argument passed with coalesce function in PostgreSQL.
How COALESCE Function works in PostgreSQL?
- We have substitute any default value data into null values by using a coalesce function in PostgreSQL.
- It is used to return the first value which is not null in table.
- If the table have multiple value and column row contains a record which have null values but in result we have not required any null value at that time we have used this function.
- Coalesce function is very important and useful in PostgreSQL to return first value which was not null in column.
- We have assume zero or any values of column row in PostgreSQL, to find the actual result of a table using coalesce function.
- This function will evaluate values or arguments from left to right to find the non-null value or argument. After finding first argument it will not evaluate the remaining argument.
- Coalesce function in PostgreSQL will accept a number of arguments or values but it returns first argument or values as a result which is not null. If all the argument are null then coalesce function will return the null value (Empty result) in result set.
- Coalesce functions work is same as IFNULL function in SQL.
Examples of PostgreSQL COALESCE
Given below are the examples:
We have use a discount table to describe example as follows:
Below is the data description of discount table which we have used to describe example.
Code:
select * from discount;
Output:
Example #1
In below example we are passing values as 10, 20, 30, 40 and 50. Coalesce function will return only first values because it finds non-null value in first place records.
Code:
select coalesce (10, 20, 30, 40, 50);
Output:
Example #2
In below example we are passing values as NULL, 20, 30, 40 and 50. Coalesce function will return second value because it was not find non-null value in first place so it will go on second value which was 20.
Code:
select coalesce (NULL, 20, 30, 40, 50);
Output:
Example #3
In below example we are passing all null values with coalesce function, it will return empty result because all value that we have passing is null.
Code:
select coalesce (NULL, NULL, NULL, NULL, NULL);
Output:
Example #4
In below example we are retrieving actual price of product. In the place of null value we have define zero value using a coalesce function .
Code:
SELECT product_name, (Product_price - COALESCE(product_discount,0)) AS actual_price FROM discount;
Output:
Advantages of using COALESCE in PostgreSQL
- Coalesce function evaluates values from left to right to find non-null value from column.
- In the place of null value we have put zero or any value to calculate result.
- We have substitute any default value data into null values by using a coalesce function in PostgreSQL.
- Coalesce function is very important and useful to return first value which was not null in column.
- It is used to return the first value which is not null in table.
Conclusion
PostgreSQL coalesce function is used for finding non-null value from column which will evaluate from left to right. After finding first argument it will not evaluate the remaining argument. We are assuming zero or any values of column row to find the actual result of a table using coalesce function.
Recommended Articles
This is a guide to PostgreSQL COALESCE. Here we discuss the introduction, how COALESCE function works? examples and advantages. You may also have a look at the following articles to learn more –
