Postgresql Coalesce

Introduction

Hi Ninjas!! Welcome to another blog on PostgreSQL.

Today we will learn about Coalesce in PostgreSQL. PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS). It is used to store data securely.

What is COALESCE?

The COALESCE function is used to handle the Null Values in a table. This function allows assigning specific values to Null Values during Database query. COALESCE conditional function can accepts number of arguments at a time ,If all the arguments are NULL in that case it returns the fist argument as not null.

Syntax:

COALESCE (argument_1, argument_2, …);

Example:

Consider the Following table:

Select Name, COALESCE(Marks, 0) as Marks from student;

In this example row 11 contains a null value. Using COALESCE on column marks the null value is replaced with 0.

Output:

Example:

Consider table Placement_cell :

Here St_id 1011 contains a NULL value in the company column.

select company,coalesce(company,'NoINFO') from Placement_cell;

This query replaces the NULL value in company column to ‘NoINFO’.

OUTPUT:

Frequently Asked Questions

How to check multiple conditions in PostgreSQL?

You can check multiple conditions in PostgreSQL by using AND operator.

What is $$ in PostgreSQL?

The $$ is the specific substitute for using single quotes to avoid escaping of nested single quotes (recursively).

What is the use of % in SQL?

The percent sign (%) represents zero, one, or multiple characters.