Handling NULL and Empty Strings in PostgreSQL CSV Imports

An empty cell means something different to every system that reads it: unknown, not applicable, zero length or "the person left it blank". PostgreSQL has a precise rule for CSV and four options to change it, and the only way to be sure what your table holds is to test each against a file that has all the cases in it. This article uses a four row file with an unquoted empty, a quoted empty, the literal NA and the text \N, and shows what each option does, including the failure when an option changes the NULL marker. The broader question of what an empty field is in any reader is in the CSV format guide.

The test file

Each row exercises one way of writing "nothing" in the note column. The qty column is an integer that is empty in row 2.

id,name,note,qty
1,Ana,,5
2,Bo,"",
3,Cy,NA,7
4,Dee,\N,8

Row 1 has an unquoted empty note. Row 2 has a quoted empty note and an empty qty. Row 3 has the text NA. Row 4 has the characters \N, which is how PostgreSQL's text format writes NULL. The query I use after each load shows whether note is NULL, whether it is an empty string and what it holds, so that the two are not confused when printed.

The default

psql -q -c "TRUNCATE n" -c "\copy n FROM 'nulls.csv' WITH (FORMAT csv, HEADER true)" -c "SELECT id, note IS NULL AS note_is_null, note = '' AS note_empty, coalesce(note, '<NULL>') AS note, qty FROM n ORDER BY id"

 id | note_is_null | note_empty |  note  | qty 
----+--------------+------------+--------+-----
  1 | t            |            | <NULL> |   5
  2 | f            | t          |        |    
  3 | f            | f          | NA     |   7
  4 | f            | f          | \N     |   8
(4 rows)

Row 1, the unquoted empty, is NULL: note_is_null is true. Row 2, the quoted empty, is an empty string: note_empty is true and note_is_null is false. Rows 3 and 4 are the literal text NA and \N. The empty qty in row 2 loaded as NULL, which for an integer column is the only way an empty cell can load. This is the behavior the COPY reference describes: the NULL option defaults to an unquoted empty string in CSV format.

Two things follow. If the writer of your file quotes empty strings and leaves NULLs unquoted, as PostgreSQL's own COPY TO and DuckDB do, the difference survives the round trip. And if the writer is a spreadsheet or a Python script that writes both the same, as Python's csv module does, the difference was never in the file and you are choosing how to read a blank.

FORCE_NULL: quoted empties become NULL

FORCE_NULL (col) applies the NULL test to a column even when the value is quoted. A quoted string that matches the NULL string becomes NULL:

psql -q -c "TRUNCATE n" -c "\copy n FROM 'nulls.csv' WITH (FORMAT csv, HEADER true, FORCE_NULL (note))" -c "SELECT id, note IS NULL AS note_is_null, note = '' AS note_empty, coalesce(note, '<NULL>') AS note, qty FROM n ORDER BY id"

 id | note_is_null | note_empty |  note  | qty 
----+--------------+------------+--------+-----
  1 | t            |            | <NULL> |   5
  2 | t            |            | <NULL> |    
  3 | f            | f          | NA     |   7
  4 | f            | f          | \N     |   8
(4 rows)

Rows 1 and 2 are now both NULL. Rows 3 and 4 are unchanged, since NA and \N are not the NULL string. This is the option I reach for when a source quotes everything: a file written with QUOTE_ALL has "" in every empty cell, and without FORCE_NULL every blank in a numeric or date column fails with invalid input syntax. The option takes a list of columns, and each one you leave out keeps the default.

FORCE_NOT_NULL: unquoted empties stay empty

The inverse. FORCE_NOT_NULL (col) stops the NULL conversion for the column and keeps an unquoted empty as an empty string:

psql -q -c "TRUNCATE n" -c "\copy n FROM 'nulls.csv' WITH (FORMAT csv, HEADER true, FORCE_NOT_NULL (note))" -c "SELECT id, note IS NULL AS note_is_null, note = '' AS note_empty, coalesce(note, '<NULL>') AS note, qty FROM n ORDER BY id"

 id | note_is_null | note_empty | note | qty 
----+--------------+------------+------+-----
  1 | f            | t          |      |   5
  2 | f            | t          |      |    
  3 | f            | f          | NA   |   7
  4 | f            | f          | \N   |   8
(4 rows)

Both rows 1 and 2 are empty strings, note_is_null false throughout. Use it when the column is NOT NULL by design, or when downstream code compares with = '' and a NULL would make the comparison unknown. It applies per column, so a numeric column in the same file is still read with the default rule: the empty qty in row 2 loaded as NULL again.

NULL 'NA': a different marker

Many sources write missing data as NA, N/A, NULL or -. The NULL option names the string that means NULL, and it replaces the default, so the unquoted empty is no longer NULL:

psql -q -c "TRUNCATE n" -c "\copy n FROM 'nulls.csv' WITH (FORMAT csv, HEADER true, NULL 'NA')" -c "SELECT id, note IS NULL AS note_is_null, note = '' AS note_empty, coalesce(note, '<NULL>') AS note, qty FROM n ORDER BY id" 2>&1

ERROR:  invalid input syntax for type integer: ""
CONTEXT:  COPY n, line 3, column qty: ""
 id | note_is_null | note_empty | note | qty 
----+--------------+------------+------+-----
(0 rows)

The load failed: invalid input syntax for type integer: "" at line 3, column qty. With NULL 'NA', only NA is NULL, so the empty qty in row 2 is the empty string, and an empty string is not an integer. The rows loaded before the error were rolled back, as the table shows. This is the most common surprise with the option, and the fix is to normalize the file before loading, so that one marker means NULL, or to load the column as text into a staging table and convert with NULLIF, as in the staging article:

SELECT id, NULLIF(NULLIF(note, ''), 'NA') AS note, NULLIF(qty, '')::int AS qty FROM stage;

If a column can be missing in more than one way, that expression is clearer than any option combination, and it keeps the rule in SQL where it can be reviewed.

The text \N in a CSV

PostgreSQL's text format uses \N for NULL, and people expect the same in CSV. It is not so. In CSV mode the NULL string is empty unless you say otherwise, so row 4 loaded as the two character string \N, as the default output showed. If your file really uses \N, which happens when it was written by COPY in text format or by MySQL's SELECT ... INTO OUTFILE, pass NULL '\N', and keep in mind that a column that is \N in the file then has no empty string at all. The TSV article explains how those two dialects differ.

Empty means default

PostgreSQL 16 added a DEFAULT option that names a string meaning "use the column's default value". It solves a different problem from NULL: a row that should get the default, not nothing. With a table where qty defaults to 1, and the file writing \D for a default value:

psql -q -c "DROP TABLE IF EXISTS d" -c "CREATE TABLE d (id int, name text, qty int DEFAULT 1)"
printf 'id,name,qty\n1,Ana,\\D\n2,Bo,5\n' > defaults.csv
psql -c "\copy d FROM 'defaults.csv' WITH (FORMAT csv, HEADER true, DEFAULT '\D')" -c "SELECT * FROM d ORDER BY id"

COPY 2
 id | name | qty 
----+------+-----
  1 | Ana  |   1
  2 | Bo   |   5
(2 rows)

Row 1 got qty 1 from the column default, and row 2 got its own 5. It is a useful way for a generator to say "leave this to the database", and it is separate from NULL, so a file can contain all three: a value, NULL and "use the default".

How to tell it worked

After a load, count each of the three states in every column that could have them: NULL, empty string and the literal markers.

SELECT count(*) FILTER (WHERE note IS NULL)  AS nulls,
       count(*) FILTER (WHERE note = '')     AS empties,
       count(*) FILTER (WHERE note IN ('NA', 'N/A', '\N', 'null')) AS markers
FROM n;

A nonzero markers count means a string that you meant as NULL is sitting in the table as text, which will pass NOT NULL and fail every IS NULL test. A nonzero empties count on a column that should not have blanks means the file quoted something.

When the file does not distinguish them

Many files do not. A CSV written by a spreadsheet or by Python's default writer puts the same bytes in for NULL and for an empty string, and no option can recover what the writer collapsed. In that case choose a rule for the column and write it down: "empty means NULL" for numbers and dates, where an empty string is not valid anyway, and a deliberate choice for text columns, where both can exist. Most analysts want NULL, since the aggregates ignore it, and most application developers want an empty string for text, since it avoids null checks.

If the source can be changed, ask it to quote empty strings and leave NULLs bare, or to use an explicit marker, and say so in the contract. If you are going on to other databases, remember the table in the format guide: DuckDB reads both forms as NULL by default, Python as an empty string, and pandas as NaN, so a rule that works in PostgreSQL does not transfer.