Materialize Documentation
s
Join the Community github/materialize

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:

Example

COPY t FROM STDIN WITH (DELIMITER '|');
COPY t FROM STDIN (FORMAT CSV);
COPY t FROM STDIN (DELIMITER '|');
Did this info help?
Yes No