COPY FROM copies data into a table using the Postgres
|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.|
The following options are valid within the
|Name||Value type||Default value||Description|
||Sets the input formatting method. For more information see Text formatting, CSV formatting.|
||Single-quoted one-byte character||Format-dependent||Overrides the format’s default column delimiter.|
||Single-quoted strings||Format-dependent||Specifies the string that represents a NULL value.|
||Single-quoted one-byte character||
||Specifies the character to signal a quoted string, which may contain the
||Specifies the character to allow instances of the
||Specifies that the file contains a header line with the names of each column in the file. The first line is ignored on input.
QUOTE must use distinct values.
As described in the Text Format section of PostgreSQL’s documentation.
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.
COPY t FROM STDIN WITH (DELIMITER '|');
COPY t FROM STDIN (FORMAT CSV);
COPY t FROM STDIN (DELIMITER '|');
The privileges required to execute this statement are:
USAGEprivileges on the schema containing the table.
INSERTprivileges on the table.
You can only copy up to 1 GiB of data at a time. If you need this limit increased, please chat with our team.