PostgreSQL CSV Versus Text Format in COPY and Which to Use

PostgreSQL's COPY has been loading and unloading files for as long as the database has existed, and it speaks two text dialects that are easy to confuse because both put one row on a line. Without any options it uses the original text format, tab-separated with backslash escapes. With FORMAT csv it uses something much closer to RFC 4180. The two are not interchangeable, and the mistake of reading one with the settings for the other produces data that loads without any error and is wrong. This article puts the same three rows through both formats on a real server, shows what each writes, what happens when you cross them, and how to choose. The CSV side of COPY in the everyday case is covered in loading a CSV file into PostgreSQL and the COPY command; this is about the choice.

The same rows in both formats

The table has an integer, a name, a note and a phone number, and three rows chosen to hit every special case: a comma and a double quote, a line break, a tab and a backslash inside values, and a NULL next to an empty string.

CREATE TABLE t (id int, name text, note text, phone text);
INSERT INTO t VALUES
 (1, 'Smith, J', 'He said "hi"', NULL),
 (2, 'Line' || chr(10) || 'Break', 'tab' || chr(9) || 'here', ''),
 (3, 'back' || chr(92) || 'slash', NULL, '555');

COPY t TO STDOUT (FORMAT csv);
COPY t TO STDOUT;

With tabs shown as ^I and line ends as $, which is how cat -A displays them, the CSV output was:

1,"Smith, J","He said ""hi""",$
2,"Line$
Break",tab^Ihere,""$
3,back\slash,,555$

and the text-format output was:

1^ISmith, J^IHe said "hi"^I\N$
2^ILine\nBreak^Itab\there^I$
3^Iback\\slash^I\N^I555$

Look at the differences row by row, because each one is a rule.

The delimiter. Text format uses a tab and nothing else, by default. CSV uses a comma, and you can change it for either.

Quoting. CSV wraps a field in double quotes when it contains the delimiter, a quote, a newline or a carriage return, and doubles the quotes inside. Row 1 shows "Smith, J" and "He said ""hi""". Text format has no quoting at all, so the comma in Smith, J is an ordinary character, and the quote in He said "hi" is too.

Line breaks inside a value. CSV keeps a real line break inside the quotes: row 2's name spans two physical lines, so a line count of the file gives 4 where the table has 3 rows. Text format escapes it as the two characters \n and keeps one physical line per row. Anyone who counts rows with wc -l gets the right answer on text and the wrong one on CSV.

Backslash and tab. In text format, a backslash in the data is written \\ and a tab as \t, because backslash is the escape character. In CSV format, a backslash is just a character, so back\slash is written unchanged and the tab in tab^Ihere appears raw. Raw tabs in a comma-delimited file are harmless, and raw tabs in a tab-delimited file would break it, which is why text format escapes them.

NULL versus empty string. Row 1's phone is NULL and row 2's is an empty string. In CSV, NULL is an unquoted empty field and the empty string is "", so the file can tell them apart. In text format, NULL is \N and the empty string is nothing between two tabs. Both formats preserve the distinction, with different markers, and each can be changed with the NULL option.

What a mismatch loads

The failure worth seeing is reading text-format output with CSV settings, which people do when they pipe COPY ... TO STDOUT into a tool that "reads delimited files". Piping the text output above through psql into a new table with FORMAT csv, DELIMITER E'\t' (the psql reference covers STDIN and \copy) succeeded without an error and gave:

 id |    name     | phone | phone_is_null 
----+-------------+-------+---------------
  1 | Smith, J    | \N    | f
  2 | Line\nBreak |       | t
  3 | back\\slash | 555   | f

Row 1's phone, which was NULL, is now the two-character string \N and phone_is_null is false, so a query for missing phone numbers misses it. Row 2's empty phone, which was an empty string, became NULL, because in CSV mode an unquoted empty field is NULL. Row 2's name contains a literal backslash and an n, and row 3's name has two backslashes, since CSV mode does not interpret escapes. Every defect is silent, and every row loaded.

The opposite mismatch is no better. Feeding RFC-style CSV to text mode leaves the quotes in the values, splits Smith, J correctly only by luck of the delimiter, and reads a quoted empty string as the two characters "". If a load surprises you with stray quotes or backslashes, check the format option first.

Choosing

The decision turns on who reads or writes the file.

I default to FORMAT csv for anything that leaves the database, with the delimiter, quote, escape and NULL options written out, and to the plain default only for throwaway dumps that go straight back into PostgreSQL. The options for CSV are DELIMITER, NULL, HEADER, QUOTE, ESCAPE, FORCE_QUOTE, FORCE_NOT_NULL and FORCE_NULL, and writing them explicitly keeps the next reader from guessing. FORCE_QUOTE * quotes every non-NULL value, which helps a consumer that cannot tell "" from nothing.

The end-of-data marker

A detail from the documentation: both file formats define an end-of-data marker, a line holding only \.. On CSV output, a value that is a lone \. is quoted so that it cannot end a later load early. You will rarely meet it, and it is the reason a hand-built file with a line of exactly that text deserves a second look.

When neither format fits

Neither format suits data with several kinds of structure on a line, free-form text with arbitrary bytes or a source whose escaping you do not control. For JSON documents, load the whole line into a jsonb column through a staging table and parse in SQL. For files with a nonstandard escape, as in some MySQL exports that use backslash inside quotes, set ESCAPE '\' in CSV mode, which tells PostgreSQL to read a backslash before a quote as an escape (the MySQL side of the same difference).

When a file is messy enough that you cannot say which dialect it is, stop guessing and look at the bytes (telling what delimiter an unknown CSV file uses), then pick the format and options deliberately. The reference for all the options is the COPY page of the PostgreSQL manual, and the family of dialects it belongs to is mapped in CSV dialects and variants you will run into.