All Products
Search
Document Center

AnalyticDB:Use the \copy command to import data from your computer to AnalyticDB for PostgreSQL

Last Updated:Jun 21, 2024

This topic describes how to run the \copy command on the psql CLI client to import text files from your computer to an AnalyticDB for PostgreSQL instance.

Usage notes

The \copy command cannot be used to import large amounts of data in parallel because the \copy command writes data in series by using the coordinator node. If you need to import large amounts of data in parallel, you can use the data import method based on Object Storage Service (OSS). For more information about how to use OSS to import data, see Use an external table to import data from OSS.

Comparison between \copy and COPY

  • Similarities:

    The syntax of both is identical. For more information about the syntax, see COPY.

  • Differences:

    • \copy is used on psql, but COPY is used on databases.

    • \copy can be used to manage FILE, STDIN, and STDOUT files, but COPY can be used to manage only STDIN and STDOUT files.

      Note

      You must have the SUPERUSER permission to modify FILE files, but AnalyticDB for PostgreSQL does not support this permission.

Syntax

The following example demonstrates how to run the \copy command to import data to an AnalyticDB for PostgreSQL instance:

            \COPY table [(column [, ...])] FROM {'file' | STDIN}
            [ [WITH] 
            [OIDS]
            [HEADER]
            [DELIMITER [ AS ] 'delimiter']
            [NULL [ AS ] 'null string']
            [ESCAPE [ AS ] 'escape' | 'OFF']
            [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
            [CSV [QUOTE [ AS ] 'quote'] 
            [FORCE NOT NULL column [, ...]]
            [FILL MISSING FIELDS]
            [[LOG ERRORS [INTO error_table] [KEEP] 
            SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
Note

You can also execute the COPY statement in AnalyticDB for PostgreSQL by using Java Database Connectivity (JDBC) in which the CopyIn method is encapsulated. For more information about CopyIn, see Interface CopyIn.

Examples

 \COPY test1 FROM '/path/to/localfile';

References

Use the \copy command to export data to your computer