Home  /  SQL commands

COPY FROM

View as Markdown

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

Syntax

    COPY [INTO] <table_name> [ ( <column> [, ...] ) ] FROM STDIN
[[WITH] ( <option1> [=] <val1> [, ...] ] )]
;
    Syntax element Description
    <table_name> Name of an existing table to copy data into.
    ( <column> [, ...] ) If specified, 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. If not specified, 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.
    [WITH] ( <option1> [=] <val1> [, ...] )

    The following <options> are supported for the COPY FROM operation:

    Name Description
    FORMAT Sets the input formatting method. Valid input formats are TEXT and CSV. For more information see Text formatting and CSV formatting.

    Default: TEXT.
    DELIMITER A single-quoted one-byte character to use as the column delimiter. Must be different from QUOTE.

    Default: A tab character in TEXT format, a comma in CSV format.
    NULL A single-quoted string that represents a NULL value.

    Default: \N (backslash-N) in text format, an unquoted empty string in CSV format.
    QUOTE For FORMAT CSV only. A single-quoted one-byte character that 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. Must be different from DELIMITER.

    Default: ".
    ESCAPE For FORMAT CSV only. A single-quoted string that specifies the character to allow instances of the QUOTE character to be parsed literally as part of a column’s value.

    Default: QUOTE’s value.
    HEADER For FORMAT CSV only. A boolean that specifies that the file contains a header line with the names of each column in the file. The first line is ignored on input.

    Default: false.    		 
    COPY [INTO] <table_name> [ ( <column> [, ...] ) ] FROM [<s3 URI> | <http URL>]
[[WITH] ( <option1> [=] <val1> [, ...] ] )]
;
    Syntax element Description
    <table_name> Name of an existing table to copy data into.
    ( <column> [, ...] ) If specified, 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. If not specified, 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.
    <s3 URI> The unique resource identifier (URI) of the Amazon S3 bucket (and prefix) to retrieve the file(s) to be copied from. If using an s3 URI, an AWS connection must be provided in the WITH clause.
    <HTTP URL> The URL (for example, s3 presigned URL) to retrieve the file(s) to be copied from.
    [WITH] ( <option1> [=] <val1> [, ...] )

    The following <options> are supported for the COPY FROM operation:

    Name Value type Default value Description
    FORMAT CSV None, must be provided Sets the input formatting method. For more information see formatting details below.
    DELIMITER Single-quoted one-byte character Format-dependent Overrides the format’s default column delimiter. FORMAT CSV only
    NULL Single-quoted strings Format-dependent Specifies the string that represents a NULL value. FORMAT CSV only
    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 false 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.
    AWS CONNECTION connection_name The name of the AWS connection to use in the COPY FROM command. If using an s3 URI, must be specified. For details on creating connections, check the CREATE CONNECTION documentation page. Only valid with S3.
    FILES array A list of files to be appended to the URI. Example: [ "top.csv", "files/a.csv", "files/b.csv" ].
    PATTERN string A glob used to identify files at at the URI. Example: "files/**".

    Note that DELIMITER and QUOTE must use distinct values.

    Details

    S3 Bucket IAM Policies

    To use COPY FROM with S3, you need to allow the following actions in your IAM policy:

    Action type Action name Action description
    Read s3:GetObject Grants permission to retrieve an object from a bucket.
    List s3:ListBucket Grants permission to list some or all of the objects in a bucket.
    NOTE: For S3-compatible object storage services (e.g., Google Cloud Storage, Cloudflare R2, MinIO), you need to enable equivalent permissions on the service you are using. The specific configuration steps will vary by provider, but the access credentials must allow the same read and list operations on the target bucket.

    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.

    • Quote characters must immediately follow a delimiter to be treated as expected.

    • 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.

    Limits

    You can copy up to 10 GiB of data at a time. If you need to copy more than that, please contact support.

    Examples

    From STDIN

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

    COPY t FROM STDIN (FORMAT CSV);

    COPY t FROM STDIN (DELIMITER '|');

    From AWS S3

    Using AWS connection

    Perform bulk import:

    Using FILES option:

    COPY INTO csv_table FROM 's3://example_bucket' (FORMAT CSV, AWS CONNECTION = example_aws_conn, FILES = ['example_data.csv']);

    Using the full s3 URI:

    COPY INTO csv_table FROM 's3://example_bucket/example_data.csv' (FORMAT CSV, AWS CONNECTION = example_aws_conn);

    Using S3-compatible object storage

    You can use COPY FROM with any S3-compatible object storage service, such as Google Cloud Storage, Cloudflare R2, or MinIO. First, create an AWS connection for S3-compatible storage, then use it in the COPY command. Make sure your credentials have the necessary permissions as described in S3 Bucket IAM Policies.

    COPY INTO csv_table FROM 's3://my_bucket/my_data.csv' (FORMAT CSV, AWS CONNECTION = gcs_connection);

    Using presigned URL

    COPY INTO csv_table FROM '<s3 presigned URL>' (FORMAT CSV);

    Privileges

    The privileges required to execute this statement are:

    • USAGE privileges on the schema containing the table.
    • INSERT privileges on the table.
    Back to top ↑