{"id":707,"date":"2024-12-07T11:06:33","date_gmt":"2024-12-07T04:06:33","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=707"},"modified":"2025-01-03T16:20:35","modified_gmt":"2025-01-03T09:20:35","slug":"postgresql-coalesce","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-coalesce\/","title":{"rendered":"PostgreSQL COALESCE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL COALESCE()<\/code> function to handle NULL<\/code> in the database.<\/p>\n\n\n\n

Getting Started with PostgreSQL COALESCE() function #<\/a><\/h2>\n\n\n\n

In PostgreSQL, NULL<\/a><\/code> means an unknown value. NULL<\/code> indicates that the data is unknown or missing when recording. <\/p>\n\n\n\n

The PostgreSQL COALESCE()<\/code> function takes a list of arguments and returns the first non-NULL<\/code> one. The following shows the syntax of the COALESCE()<\/code> function:<\/p>\n\n\n

COALESCE(value1, value2, ...)<\/code><\/span><\/pre>\n\n\n
The COALESCE<\/code> function accepts a variable number of arguments (value1<\/code>, value2<\/code>, …). It evaluates arguments from left to right and returns the first one that is not NULL<\/code>.<\/p>\n\n\n\n
The COALESCE<\/code> function returns NULL<\/code> if all arguments are NULL<\/code>.<\/p>\n\n\n\n
PostgreSQL COALESCE() function examples #<\/a><\/h2>\n\n\n\n
We’ll use the profiles<\/code> table from the inventory<\/code> database for the demonstration purposes.<\/p>\n\n\n\n
The following example retrieves the first name and work phone number from user profiles:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  work_phone\nFROM<\/span>\n  profiles\nORDER<\/span> BY<\/span>\n  first_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name |  work_phone\n------------+--------------\n Alice      | NULL<\/span>\n Bob        | NULL<\/span>\n Charlie    | NULL<\/span>\n Jane       | 408<\/span>-456<\/span>-7891<\/span>\n John       | 408<\/span>-456<\/span>-7890<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that Alice<\/code>, Bob<\/code>, and Charlie<\/code> have work phone numbers as NULL<\/code>.<\/p>\n\n\n\n
To make the list more business-friendly, you can use the COALESCE<\/code> function to replace NULL<\/code> with N\/A (Not Available).<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  COALESCE(work_phone, 'N\/A'<\/span>) work_phone\nFROM<\/span>\n  profiles\nORDER<\/span> BY<\/span>\n  first_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name |  work_phone\n------------+--------------\n Alice      | N\/A\n Bob        | N\/A\n Charlie    | N\/A\n Jane       | 408-456-7891\n John       | 408-456-7890<\/code><\/span><\/pre>\n\n\n
In this example, the COALESCE<\/code> returns the work phone if it is not NULL<\/code> or N\/A if it is NULL<\/code>.<\/p>\n\n\n\n
Suppose you print out a contact list for all users, including first name and phone number. You can use the home phone number if the work phone number is unavailable.<\/p>\n\n\n\n
To do that, you can use the COALESCE<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  COALESCE(work_phone, home_phone) phone\nFROM<\/span>\n  profiles\nORDER<\/span> BY<\/span>\n  first_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name |    phone\n------------+--------------\n Alice      | 408<\/span>-111<\/span>-4444<\/span>\n Bob        | 408<\/span>-111<\/span>-5555<\/span>\n Charlie    | NULL<\/span>\n Jane       | 408<\/span>-456<\/span>-7891<\/span>\n John       | 408<\/span>-456<\/span>-7890<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, the COALESCE<\/code> will return the work phone number if it is not null or the home phone number.<\/p>\n\n\n\n
The output indicates that Charlie<\/code> does not have work and home phone numbers.<\/p>\n\n\n\n
You can replace that NULL<\/code> with N\/A in the COALESCE<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  COALESCE(work_phone, home_phone, 'N\/A'<\/span>) phone\nFROM<\/span>\n  profiles\nORDER<\/span> BY<\/span>\n  first_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name |    phone\n------------+--------------\n Alice      | 408-111-4444\n Bob        | 408-111-5555\n Charlie    | N\/A\n Jane       | 408-456-7891\n John       | 408-456-7890<\/code><\/span><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n