A plain-text dump from PostgreSQL’s pg_dump carries a table’s rows in a
COPY block: one statement, followed by the rows as lines of data, followed
by a line that marks the end. This is the only part of a dump that is not SQL.
The block
COPY public.fruit (id, name, note) FROM stdin;
1 apple \N
2 pear has a tab\there
3 plum two\nlines
\.The statement ends at its semicolon. The data starts on the next line. Each line after
that is one row, with columns in the order the statement lists them, up to a line
holding only \.. When psql runs the script, it reads that
line as the end of the data.
The text format
Unless the statement says otherwise, COPY uses its text format, in which
columns are separated by a tab. A value equal to \N is
NULL. An empty field is an empty string, not NULL.
A backslash in a value starts a sequence:
| Sequence | Means |
|---|---|
\b | Backspace |
\f | Form feed |
\n | Newline |
\r | Carriage return |
\t | Tab |
\v | Vertical tab |
\ and one to three octal digits | The byte with that code |
\x and one or two hex digits | The byte with that code |
\ and any other character | That character, so \\ is a backslash |
COPY TO never writes the octal or hex forms, but it does write the
others. A newline inside a value is written as \n, so a line of a dump is
always one row.
Options
Options change the format of the data. pg_dump uses none of them, but a
hand-written script can.
| Option | Default in text format | Default in CSV format |
|---|---|---|
DELIMITER | Tab | Comma |
NULL | \N | An empty string with no quotes |
QUOTE | Does not apply | Double quote |
ESCAPE | Does not apply | The same as QUOTE, so a quote is doubled |
COPY t (a, b) FROM stdin WITH (FORMAT csv);
1,"quoted, with a comma"
2,
\.In CSV format the backslash has no special meaning. psql still ends the
data at a line holding only \., whatever the format, when the data
is in the script.
There is also a binary format, but it cannot be written inside a text script.
Where COPY appears
In a dump a COPY block is the whole of a table’s data. It comes between
the CREATE TABLE and the commands that add primary keys and foreign keys,
so the rows are loaded before the keys are built.
A block can be empty, with no lines between the statement and \., when a
table has no rows.