COALESCE()

Procedural SQL (PSQL) StatementsAggregate Functions

`COALESCE()`

Returns the first non-NULL argument

Result type

Depends on input

Syntax

COALESCE (<exp1>, <exp2> [, <expN> ... ])

Table 1. `COALESCE` Function Parameters
Parameter	Description
exp1, exp2 … expN	A list of expressions of compatible types

The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument.If all the arguments evaluate to NULL, the result is NULL.

`COALESCE` Examples

This example picks the Nickname from the Persons table.If it happens to be NULL, it goes on to FirstName.If that too is NULL, “'Mr./Mrs.'” is used.Finally, it adds the family name.All in all, it tries to use the available data to compose a full name that is as informal as possible.This scheme only works if absent nicknames and first names are NULL: if one of them is an empty string, COALESCE will happily return that to the caller.That problem can be fixed by using [fblangref50-scalarfuncs-nullif].

select
  coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    as FullName
from Persons

COALESCE()

COALESCE()

COALESCE Examples

`COALESCE()`

`COALESCE` Examples