Fallbacks with COALESCE
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 14 of 20.
COALESCE returns the first argument that is not NULL. Use it when a missing value needs a display fallback. It does not replace other values that happen to look empty.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, COALESCE(note, 'No note') AS display_note FROM products ORDER BY id;Map displays No note; Notebook keeps Recycled. An empty string would remain an empty string because it is a non-null value. The source note remains unchanged.
COALESCE(value, fallback) replaces NULL in the result, while keeping zero and empty text.
Challenge
EasyReturn id and shipping_note, using the existing note when present and Pack carefully when missing. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
3Build Useful Results
Calculate and Name ColumnsUnique Results with DISTINCTLabels with CASEFallbacks with COALESCEPacking LabelsPractice on your own: PostgreSQL playground