In PostgreSQL's COPY, how do the text, csv and binary formats differ, and how does each one represent a NULL value?
answer
- three FORMAT values, one default
- backslash escapes versus quoting
- an empty unquoted CSV field
- a length word of -1
basics
~20 stext, the default, is tab-delimited with backslash escapes and \N for NULL; csv uses commas and quoting, so an unquoted empty field is NULL and "" an empty string; binary is type-specific and marks NULL with length -1.
solid answer
~50 s`FORMAT text` is the default: one line per row, a tab between columns, special characters escaped with backslashes, and `\N` for NULL. `FORMAT csv` follows the spreadsheet convention: comma-delimited, values wrapped in double quotes when needed, quotes doubled inside values, and no backslash processing. CSV has no standard NULL, so PostgreSQL reads an **unquoted empty field** as NULL and a quoted `""` as an empty string; `FORCE_NULL` and `FORCE_NOT_NULL` adjust that per column, and `HEADER MATCH` checks the header line against the table's column names. `FORMAT binary` stores each value in its type's binary form behind a length word, `-1` meaning NULL: somewhat faster, but tied to exact column types, less portable across architectures and versions, and it rejects `DELIMITER`, `NULL`, `HEADER` and `ON_ERROR ignore`. In PostgreSQL 18 a lone `\.` line in a CSV file is data, no longer end-of-input.
code
sql · 12 linesCREATE UNLOGGED TABLE census_staging (
person_id bigint,
household_id bigint,
region_code text,
birth_date date,
income numeric(12,2)
);
-- Export has a header row and writes NA for unknown values
COPY census_staging
FROM '/srv/import/census_2026.csv'
WITH (FORMAT csv, HEADER MATCH, NULL 'NA', FORCE_NOT_NULL (region_code));go deeper
Recall the three FORMAT values, that text is the default, and that text writes NULL as backslash-N while CSV uses an unquoted empty field.
Explain how CSV separates NULL from an empty string by quoting, what FORCE_NULL and FORCE_NOT_NULL change, and why HEADER MATCH protects a load from a reordered export.
Show judgement on format choice: CSV for files from other systems, binary only between identical PostgreSQL column types, and awareness of encoding, DateStyle and 18's CSV end-marker change.
Weigh interchange format as a contract between teams: who owns the NULL convention, how header checks catch schema drift, and when binary's speed is worth its brittleness.
## The three formats at a glance PostgreSQL's `COPY` reads and writes three wire formats, chosen with the `FORMAT` option. They differ in how columns are separated, how special characters are protected and, the part interviewers like, how NULL is told apart from an empty string. | Property | `text` | `csv` | `binary` | |---|---|---|---| | Default? | **yes** | no | no | | Column separator | tab | comma | none: each field has a 32-bit length word | | NULL is written as | `\N` | an unquoted empty field | length word `-1` | | Protecting special characters | backslash escapes | `QUOTE` (`"`) with doubled quotes | not needed | | `HEADER` / `HEADER MATCH` | allowed | allowed | not allowed | | `ON_ERROR ignore` | allowed | allowed | not allowed | | Portability | any PostgreSQL version | any version, other tools | identical column types; less portable across architectures and versions | ## text format - One line per row, columns separated by the **`DELIMITER`**, a tab unless you choose otherwise. `COPY FROM` raises an error if a line has more or fewer columns than expected. - **NULL is `\N`** by default. `COPY FROM` compares the raw field with the null string *before* removing backslashes, so a real data value `\N` must be written `\\N` and cannot be confused with NULL. - Backslash itself, newline, carriage return and the delimiter **must** be backslash-escaped inside a value; the manual recommends writing data newlines as `\n` and carriage returns as `\r`. - A line holding only `\.` is an end-of-data marker, which matters for inline data in a script and is unnecessary when reading a file. ## csv format - **`DELIMITER`** defaults to a comma, **`QUOTE`** to a double quote, and **`ESCAPE`** to the quote character, so a quote inside a quoted value is doubled. Backslash is an ordinary character. - CSV has no standard NULL. PostgreSQL's rule: the **null string defaults to an unquoted empty string**, so `7,,2026` loads NULL in the middle column, while `7,"",2026` loads a zero-length string. On output, NULL is written unquoted and an empty string as `""`. - **`FORCE_NOT_NULL (cols)`** stops the listed columns being matched against the null string, so empty fields become empty strings; **`FORCE_NULL (cols)`** also turns a *quoted* value matching the null string into NULL. Both are `COPY FROM` only; `FORCE_QUOTE` is their `COPY TO` counterpart. - **`HEADER`** skips the first line on input and writes column names on output; **`HEADER MATCH`** (input only) fails unless the header's column count and names match the table's columns in order, which catches an export that reordered its columns. - Quoted values may contain newlines, so a CSV row can span several lines. Every character is significant: whitespace padding around a quoted value becomes part of the data or an error. ## binary format - A file header, then each row as a field count followed by fields, each a **32-bit length word plus that many bytes**; a length of **`-1` means NULL**. Everything is in network byte order. - It is **somewhat faster** because no text parsing happens, but it is **type-specific**: a file written from a `smallint` column will not load into an `integer` column, which text format would accept without complaint. - It is less portable across machine architectures and PostgreSQL versions, and it rejects `DELIMITER`, `NULL` and `HEADER`. `ON_ERROR` may only be `stop`; asking for `ignore` fails with `only ON_ERROR STOP is allowed in BINARY mode`. ## Settings that change what the bytes mean 1. **Encoding.** Input is read in the `ENCODING` option's encoding, or the session's `client_encoding` if omitted, even when the server reads a file directly. 2. **DateStyle.** Date input and output follow `DateStyle`; set it to `ISO` before `COPY TO` if another installation will read the file. 3. **The end-of-data marker in CSV (PostgreSQL 18).** Before 18, an unquoted line containing only `\.` ended CSV input even when reading a file. Since 18 the server treats it as data in CSV; psql still uses it to end inline `COPY FROM STDIN` data, and older psql `\copy` clients can misbehave against an 18 server. `COPY TO` still quotes a lone `\.` for compatibility. ## Choosing for the census CSV A census export is a CSV, so `FORMAT csv` with `HEADER MATCH` is the natural choice: a reordered export fails at once instead of loading birth dates into the income column. If the export writes `NA` for unknown values, set `NULL 'NA'`, and use `FORCE_NOT_NULL` on any text column where `NA` is a real value. Keep `binary` for PostgreSQL-to-PostgreSQL transfers between tables with identical column types, where its speed is worth its rigidity.
- In PostgreSQL's CSV COPY FROM, how do you load empty fields as empty strings rather than NULL?List those columns in `FORCE_NOT_NULL (col, ...)`, or use `*` for every column: they are no longer matched against the null string, so an unquoted empty field becomes a zero-length string. The opposite, `FORCE_NULL`, turns a quoted value matching the null string into NULL. Both are CSV-only and `COPY FROM`-only, and they may be combined on one column.
- What changed for the \. line in PostgreSQL 18's CSV COPY FROM?Before 18, an unquoted line holding only `\.` ended CSV input even when the server read a file. Since 18 it is treated as data in CSV files, and `\.` must also stand alone on its line to act as a marker. psql still ends inline `COPY FROM STDIN` data at that line, and older psql `\copy` clients can misbehave against an 18 server.
- CSV is the default COPY format when no FORMAT option is given.
- In CSV format a quoted empty field "" is read as NULL by default.
- A binary COPY file from smallint columns loads fine into integer columns.
- Binary COPY files are portable across any PostgreSQL version and CPU architecture.
- ON_ERROR ignore works with every COPY format, binary included.
Assumes PostgreSQL 18; since 18, COPY FROM no longer treats a lone \. line as end-of-file when reading CSV from a file.