How to use the COPY command to export data to a local file - AnalyticDB

The \copy command in psql lets you export data from an AnalyticDB for PostgreSQL instance directly to a file on your local computer—without requiring SUPERUSER privileges.

Prerequisites

Before you begin, make sure you have:

A running AnalyticDB for PostgreSQL instance
psql installed and configured to connect to your instance (see Client connection)

Data Management (DMS) does not support the \copy command. Use psql to run \copy.

How it works

\copy is a psql meta-command that runs the underlying SQL COPY command but routes data through the client rather than the server. Instead of the server reading or writing the specified file, psql reads or writes the file and routes data between the server and the local filesystem. This means file access and permissions are controlled by the local user, not the database server—so no SUPERUSER permission is needed.

`\copy` vs `COPY`:

	`\copy`	`COPY`
Where it runs	psql client	Database server
File access	Local filesystem (FILE, STDIN, STDOUT)	STDIN and STDOUT only
SUPERUSER required	No	Yes (for FILE access)

AnalyticDB for PostgreSQL does not support the SUPERUSER permission. Use \copy to export data to local files.

Syntax

\COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
    [ [WITH] [OIDS] [HEADER]
      [DELIMITER [ AS ] 'delimiter']
      [NULL [ AS ] 'null string']
      [ESCAPE [ AS ] 'escape' | 'OFF']
      [CSV [QUOTE [ AS ] 'quote']
           [FORCE QUOTE column [, ...]] ]
      [IGNORE EXTERNAL PARTITIONS ] ]

where the WITH options are:

Option	Description
`OIDS`	Include OIDs for each row
`HEADER`	Include a header row with column names (CSV format only)
`DELIMITER 'delimiter'`	Character separating columns, for example `,`
`NULL 'null string'`	String representing null values, for example `\N`
`ESCAPE 'escape'`	Escape character for CSV output
`CSV`	Use CSV format
`QUOTE 'quote'`	Quote character for CSV format
`FORCE QUOTE column`	Force quoting for specified columns in CSV output
`IGNORE EXTERNAL PARTITIONS`	Skip external partitions during export

For the full syntax reference, see COPY in the PostgreSQL documentation.

AnalyticDB for PostgreSQL also supports running the COPY statement via JDBC. The CopyIn method is encapsulated within JDBC. For details, see Interface CopyIn.

Export data to a local file

Connect to your instance using psql, then run \copy to export a table or query result to a local file.

Export a full table:

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

Export with CSV format and a custom delimiter:

\COPY test1 TO '/path/to/localfile.csv' WITH CSV DELIMITER ',';

Export query results:

\COPY (SELECT id, name FROM test1 WHERE status = 'active') TO '/path/to/output.csv' WITH CSV HEADER;

Replace the following placeholders with your actual values:

Placeholder	Description	Example
`test1`	Name of the table to export	`orders`
`/path/to/localfile`	Absolute path to the output file on your local machine	`/home/user/export.csv`

What's next

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