PostgreSQL: How to Convert Empty String to Null

Often in PostgreSQL you may want to convert empty strings to Null values.

You can use the NULLIF() function with the following syntax to do so:

SELECT id,
       NULLIF(team, '') AS team,
       points
FROM athletes;

This particular example will convert all empty strings in the team column of the athletes table to null values.

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

Example: How to Convert Empty Strings to Null in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, '', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, '', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

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

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 |      |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 |      |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Notice that there are several empty values in the team column of the table.

Suppose that we would like to convert each of these empty values to Null instead.

We can use the following syntax to do so:

SELECT id,
       NULLIF(team, '') AS team,
       points
FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | null |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | null |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Notice that each empty value in the team column has been converted to a null value instead.

You can use similar syntax to convert empty values to null values in a column of your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Delete Empty Rows in Table
PostgreSQL: How to Fill Null Values with Previous Value
PostgreSQL: How to Replace Null Values with String