PostgreSQL: How to Replace Null Values with String

Often in PostgreSQL you may want to replace null values in a table with a specific string instead.

You can use the COALESCE statement with the following syntax to do so:

SELECT athleteID,
       COALESCE(team, 'None') AS team,
       points,
       assists
FROM athletes;

This particular example will replace the null values with the string ‘None’ in both the team column of the table named athletes.

The following example shows how to use this syntax in practice.

Example: How to Replace Null Values with String in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  player_info TEXT.
  points INTEGER,
  assists INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 13, 5);
INSERT INTO athletes VALUES (0002, NULL, 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, NULL, 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', 22, 11);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, NULL, 26, 3);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     13 |       5
         2 |         |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 |         |     10 |       5
         6 | Nuggets |     22 |      11
         7 | Nuggets |     20 |       8
         8 |         |     26 |       3

Notice that the team column has several null values.

We can use the following syntax to replace each of the null values in the team column with the string ‘None’ instead:

SELECT athleteID,
       COALESCE(team, 'None') AS team,
       points,
       assists
FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     13 |       5
         2 | None    |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | None    |     10 |       5
         6 | Nuggets |     22 |      11
         7 | Nuggets |     20 |       8
         8 | None    |     26 |       3

Notice that each of the null values in the team column have been replaced with the string ‘None’ instead.

Feel free to use similar syntax to replace null values with a specific string in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters