How to Use \copy in psql for Importing and Exporting Data

Use the \copy command in psql to import or export data between a PostgreSQL table and a file on the client machine. It works like the SQL COPY command but runs on the client side, allowing file access without superuser rights. The syntax is \copy table_name [ ( column_list ) ] FROM|TO 'filename' [ WITH ( options ) ].

Syntax

The basic syntax of the \copy command in psql is:

• table_name: The target table to copy data to or from.
• ( column_list ): Optional list of columns to copy.
• FROM|TO: Direction of data transfer; FROM imports data into the table, TO exports data from the table.
• 'filename': Path to the file on the client machine.
• WITH ( options ): Optional parameters like FORMAT, DELIMITER, HEADER, etc.

sql

\copy table_name [ ( column_list ) ] FROM|TO 'filename' [ WITH ( option [, ...] ) ]

Example

This example shows how to export data from a table to a CSV file and then import it back using \copy.

sql

\copy employees TO 'employees.csv' WITH (FORMAT csv, HEADER true)
\copy employees FROM 'employees.csv' WITH (FORMAT csv, HEADER true)

Output

COPY 5
COPY 5

Common Pitfalls

Common mistakes when using \copy include:

• Using COPY instead of \copy in psql when you don't have superuser rights, which causes permission errors.
• Specifying incorrect file paths; \copy reads/writes files on the client machine, not the server.
• Forgetting to match the file format and delimiter options with the file content.
• Not including HEADER true when the CSV file has a header row, causing import errors.

Example of wrong and right usage:

sql

-- Wrong: Using COPY in psql without superuser rights
COPY employees TO '/tmp/employees.csv' WITH (FORMAT csv);

-- Right: Using \copy in psql (client-side)
\copy employees TO '/tmp/employees.csv' WITH (FORMAT csv)

Quick Reference

Option	Description	Example
FORMAT	Specifies file format (csv, text, binary)	WITH (FORMAT csv)
DELIMITER	Character separating columns	WITH (DELIMITER ',')
HEADER	Include header row in CSV	WITH (HEADER true)
NULL	String that represents NULL values	WITH (NULL 'NULL')
QUOTE	Character used for quoting	WITH (QUOTE '"')

Key Takeaways

Use \copy in psql to import/export data between client files and PostgreSQL tables without superuser rights.

Specify FROM to import data and TO to export data with \copy.

Always match file format and options like HEADER and DELIMITER to your data file.

File paths in \copy refer to the client machine, not the database server.

Use \copy instead of COPY in psql unless you have superuser access on the server.