Use the \copy command in the psql command-line interface (CLI) to import local text files directly into an AnalyticDB for PostgreSQL instance — no server-side file access required.
\copy writes data serially through the coordinator node. It works well for small to medium datasets but is not suitable for large-scale parallel imports. For high-volume data loading, use OSS-based external tables instead. See Use an external table to import data from OSS.
When to use \copy vs COPY
Both commands share the same syntax (see COPY in the PostgreSQL documentation), but they run in different contexts.
\copy runs on the psql client and reads files from your local machine — file access and permissions are governed by the local user, not the database server. This means no SUPERUSER privilege is required. AnalyticDB for PostgreSQL does not grant SUPERUSER permission, so server-side COPY with FILE is unavailable; use \copy instead.
\copy |
COPY |
|
|---|---|---|
| Runs in | psql client | Database server |
| Reads files from | Local machine | Server filesystem |
| Requires SUPERUSER | No | Yes (for FILE access) |
| Handles | FILE, STDIN, STDOUT | STDIN, STDOUT only |
Prerequisites
Before you begin, ensure that you have:
-
psql installed and connected to your AnalyticDB for PostgreSQL instance
-
A local text file to import
-
Write permission on the target table
Import a local file
Run \copy in your psql session to load the file into a table:
\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] ]Example: Import all rows from a local file into a table named test1.
\COPY test1 FROM '/path/to/localfile';Verify the import
After running \copy, confirm the data loaded successfully:
SELECT COUNT(*) FROM test1;If the row count matches your source file, the import is complete.
If you used LOG ERRORS, query the error table to review any rejected rows:
SELECT * FROM error_table;Use JDBC instead of psql
To import data programmatically, use Java Database Connectivity (JDBC). The PostgreSQL JDBC driver exposes the CopyIn interface, which wraps the COPY statement. See Interface CopyIn in the PostgreSQL JDBC documentation.