Home  /  Reference

COPY FROM

COPY FROM copies data into a table using the Postgres COPY protocol.

Syntax

COPY table_name ( column , ) FROM STDIN WITH ( field val , )
Field Use
table_name Copy values to this table.
(column…) Correlate the inserted rows' columns to table_name’s columns by ordinal position, i.e. the first column of the row to insert is correlated to the first named column.

Without a column list, all columns must have data provided, and will be referenced using their order in the table. With a partial column list, all unreferenced columns will receive their default value.
field The name of the option you want to set.
val The value for the option.

WITH options

The following options are valid within the WITH clause.

Name Value type Default value Description
FORMAT TEXT, CSV TEXT Sets the input formatting method. For more information see Text formatting, CSV formatting.
DELIMITER Single-quoted one-byte character Format-dependent Overrides the format’s default column delimiter.
NULL Single-quoted strings Format-dependent Specifies the string that represents a NULL value.
QUOTE Single-quoted one-byte character " Specifies the character to signal a quoted string, which may contain the DELIMITER value (without beginning new columns). To include the QUOTE character itself in column, wrap the column’s value in the QUOTE character and prefix all instance of the value you want to literally interpret with the ESCAPE value. FORMAT CSV only
ESCAPE Single-quoted strings QUOTE’s value Specifies the character to allow instances of the QUOTE character to be parsed literally as part of a column’s value. FORMAT CSV only
HEADER boolean boolean Specifies that the file contains a header line with the names of each column in the file. The first line is ignored on input. FORMAT CSV only.

Note that DELIMITER and QUOTE must use distinct values.

Details

Text formatting

As described in the Text Format section of PostgreSQL’s documentation.

CSV formatting

As described in the CSV Format section of PostgreSQL’s documentation except that:

  • More than one layer of escaped quote characters returns the wrong result. (#9074)

  • Quote characters must immediately follow a delimiter to be treated as expected. (#9075)

  • Single-column rows containing quoted end-of-data markers (e.g. "\.") will be treated as end-of-data markers despite being quoted. In PostgreSQL, this data would be escaped and would not terminate the data processing.

  • Quoted null strings will be parsed as nulls, despite being quoted. In PostgreSQL, this data would be escaped.

    To ensure proper null handling, we recommend specifying a unique string for null values, and ensuring it is never quoted.

  • Unterminated quotes are allowed, i.e. they do not generate errors. In PostgreSQL, all open unescaped quotation punctuation must have a matching piece of unescaped quotation punctuation or it generates an error.

Example

COPY t FROM STDIN WITH (DELIMITER '|');

COPY t FROM STDIN (FORMAT CSV);

COPY t FROM STDIN (DELIMITER '|');

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schema containing the table.
  • INSERT privileges on the table.

Limits

You can only copy up to 1 GiB of data at a time. If you need this limit increased, please chat with our team.

Back to top ↑