Information2 use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
The PostgreSQL COPY command is a high-performance utility for transferring data between database tables and files. Compared with individual INSERT statements, it significantly speeds up bulk imports and exports, making it a common choice for data migration, backups, and reporting.
This guide covers PostgreSQL COPY command syntax, practical examples for importing and exporting data, the differences between COPY and \copy, and solutions to common errors.
The PostgreSQL COPY command is a high-performance utility for transferring large volumes of data between database tables and files or streams. Unlike standard INSERT statements, it is designed for bulk data operations and significantly reduces the overhead of processing rows one at a time.
COPY moves structured data directly between a PostgreSQL table and a file on the server file system or an active client stream. By minimizing query parsing and network overhead, it can import or export millions of rows much faster than executing individual INSERT statements.
The direction of the data transfer depends on the option you use:
COPY ... FROM imports data from an external file into an existing PostgreSQL table. The target table must already exist because COPY does not create tables. COPY ... TO exports data from a PostgreSQL table or the results of a SQL query to an external file. It is commonly used to create CSV files, generate reports, or back up table data.
Before importing or exporting data, it is important to understand the basic PostgreSQL COPY command syntax and the available options. Knowing how these components work helps prevent formatting errors and ensures successful data transfers.
Use the following syntax to import data from a file into an existing PostgreSQL table:
COPY table_name (column1, column2, column3)
FROM '/path/to/file.csv'
[WITH (option [, ...])];
To export data from a table to a file, replace FROM with TO:
COPY table_name (column1, column2, column3)
TO '/path/to/file.csv'
[WITH (option [, ...])];
The column list is optional. If you omit it, PostgreSQL maps the file data to every column in the target table using the table’s default column order.
You can control how PostgreSQL imports or exports data by specifying options in the WITH clause.
FORMAT: Specifies the file format. Supported values are text, csv, and binary. The default is text. HEADER: Indicates that the file contains a header row. During import, PostgreSQL skips the first row. During export, it writes the column names as the first row. This option is available only with the csv format. DELIMITER: Defines the character used to separate columns. The default delimiter is a tab for text format and a comma for csv format. NULL: Specifies the string that represents a NULL value. The default is \N for text format and an unquoted empty string for csv format. QUOTE: Specifies the quotation character used for fields containing delimiters. The default is the double quotation mark (“). This option applies only to the csv format. ESCAPE: Specifies the character used to escape special characters, including the quotation character. By default, PostgreSQL uses the same character defined for QUOTE. ENCODING: Specifies the character encoding of the input or output file, such as UTF8 or LATIN1. PostgreSQL automatically converts the data if the file encoding differs from the database encoding.Transferring data between database tables and local files is one of the most frequent administrative tasks in database management. Below are practical examples for both directions.
Exporting with COPY allows you to write table data or query results to a file. Exporting to CSV is one of the most common use cases, since most spreadsheet tools and data pipelines accept this format directly.
Export an Entire Table to CSV
To dump all rows from a table into a CSV file with headers, use the TO clause and specify the csv format.
COPY employees TO '/tmp/employees.csv' WITH (FORMAT csv, HEADER);
Export Query Results
You can also export only a subset of data by passing a query inside parentheses.
COPY (SELECT id, name, department FROM employees WHERE salary > 50000) TO '/tmp/high_earners.csv' WITH (FORMAT csv, HEADER);
Export Selected Columns
If you only need a few specific fields from a table, list them in parentheses after the table name.
COPY employees (id, email) TO '/tmp/emails.csv' WITH (FORMAT csv, HEADER);
Export Without Headers
To export data without a header row, simply omit the HEADER option from your configuration parameters.
COPY employees TO '/tmp/employees_no_header.csv' WITH (FORMAT csv);
Unlike client-side tools, COPY requires the source file to sit on the same machine as the PostgreSQL server, since the command runs server-side. The target table layout should align with the incoming file structure.
Import a CSV File into a Table
To load a standard comma-separated file that contains a header row, use the FROM keyword and enable the header option.
COPY employees FROM '/tmp/employees.csv' WITH (FORMAT csv, HEADER);
Import Specific Columns
If the source CSV has fewer fields than the destination table, map the target columns explicitly so the engine matches the data correctly.
COPY employees (name, department, salary) FROM '/tmp/new_hires.csv' WITH (FORMAT csv, HEADER);
Handling NULL Values
If your source file represents missing data with a specific word or phrase, define that value using the NULL option.
COPY employees FROM '/tmp/employees_nulls.csv' WITH (FORMAT csv, HEADER, NULL 'N/A');
Import Files with Different Delimiters
When dealing with non-standard files, like those separated by semicolons instead of commas, adjust the DELIMITER parameter.
COPY employees FROM '/tmp/employees_semicolon.csv' WITH (FORMAT csv, HEADER, DELIMITER ';');
Although COPY and \copy perform similar tasks, they work in different environments. Understanding the difference between server-side and client-side execution helps you choose the right command and avoid common file access errors.
The SQL COPY command runs on the PostgreSQL server, which means the server reads from or writes to files on its own file system. To use COPY with server files, your database account typically requires superuser privileges or roles such as pg_read_server_files or pg_write_server_files.
By contrast, \copy is a meta-command provided by the psql client. It reads from or writes to files on your local machine, then streams the data to or from the PostgreSQL server. Because file access is handled by the client, \copy usually works without elevated server-side privileges.
The table below summarizes the key differences between the two commands.
| Feature | COPY | \copy |
|---|---|---|
| Where it runs | On the PostgreSQL server | In the local psql client |
| File location | Server file system | Local file system |
| Permission requirements | Superuser or server file roles | Standard database user permissions |
| Best use cases | Server‑side imports, exports, and automated data transfers | Local development, remote databases, and environments with limited server access |
COPY reads files from the database server, while \copy reads files from your local machine.These COPY command examples cover common day-to-day scenarios that database administrators and developers run into regularly.
Creating a quick backup of a specific table is a useful step before running risky update statements. This example saves the customers table to a temporary directory on the server with headers included.
COPY customers TO '/tmp/customers_backup.csv' WITH (FORMAT csv, HEADER);
When restoring a table from a previously exported file, truncating the target table first helps avoid duplicate primary key violations. Use matching options from your original export operation to read the file correctly.
TRUNCATE TABLE customers;
TRUNCATE TABLE customers;
COPY customers FROM '/tmp/customers_backup.csv' WITH (FORMAT csv, HEADER);
You can pipe data from one database directly into another using the command line with psql and client-side streaming. This approach avoids writing any intermediate files to disk, saving space and time. If the two databases are on different servers, add the -h flag to specify each host.
psql -d source_db -c "\copy orders TO STDOUT" | psql -d target_db -c "\copy orders FROM STDIN"
Instead of exporting entire tables, database reporting often needs filtered data. You can wrap complex queries in parentheses to export targeted datasets for reporting tools.
COPY (
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
) TO '/tmp/department_report.csv' WITH (FORMAT csv, HEADER);
Bulk importing and exporting can run into unexpected problems due to file access restrictions or mismatched schemas. Below are the most frequent issues developers face and the steps to resolve them.
This error occurs when the operating system user running the PostgreSQL service (usually the postgres system user) does not have read or write permissions on the directory containing your file. Even if your personal system login has full administrative permissions, the database process itself cannot touch the file.
Step 1. Move the target file to a public directory, such as /tmp or /var/tmp, where the system user can access it.
Step 2. Grant read access to the database engine by adjusting file and directory permissions, for example, running chmod 644 filename.csv on the file. Make sure the containing directory is also accessible to the postgres user.
This database message typically appears when using server-side COPY instead of client-side \copy for local files. If the path specified is correct but the file lives on a client machine, the server cannot access it.
To solve this, switch your command from SQL COPY to the client-side \copy meta-command inside the psql interface. Unlike COPY, \copy reads and writes files on the machine running psql, so it can reach files that the database server itself cannot see.
Mismatched data types cause this error when the input file contains data that does not fit the database column definition. For example, trying to insert a text string into an integer column triggers this issue.
Review your file headers to ensure the columns align with the database table layout. If your file contains an empty string instead of a number, define the NULL option in your import statement so the database engine can translate it to a null value.
This occurs when a row in the input file contains more delimiter-separated fields than the database table has columns. It often points to a delimiter mismatch, such as when your text contains a raw comma within a standard CSV file.
Wrap the problematic text field in quotation marks within your source file. You should also ensure the FORMAT csv option is explicitly specified so the engine respects the quotation characters.
An encoding error happens when the text encoding of the input file does not match the database expectations. Trying to parse a UTF-8 character in a database set to SQL_ASCII or LATIN1 commonly raises this warning.
Declare the correct source file encoding using the ENCODING option during import. For example, add ENCODING 'UTF8' to the option block of your command.
Running COPY manually works well for one-off exports, but it does not scale as a long-term backup strategy. Someone still needs to remember to run the command, store the file somewhere safe, and clean up old exports before disk space runs out.
This is where a dedicated backup solution like i2Backup becomes useful. Instead of relying on manual COPY commands and cron jobs, i2Backup handles PostgreSQL backups on a schedule, with built-in retention and recovery options.
For PostgreSQL environments running alongside other databases or virtualized infrastructure, i2Backup manages all of it from a single console, rather than requiring separate scripts for each system.
Beyond scheduled backups, businesses with stricter recovery point requirements can pair i2Backup with i2CDP, which replicates changing data at the byte level and reduces RPO to near zero. For teams managing PostgreSQL replication across multiple servers, i2Stream offers real-time database and big data replication as a complementary option.
The COPY command remains one of the fastest ways to move data in and out of PostgreSQL, whether you are exporting a table for a report or restoring data after a schema change. Understanding the difference between server-side COPY and client-side \copy helps you avoid the most common file access errors covered in this guide.
For routine backups, pairing manual COPY exports with an automated solution like Info2soft’s i2Backup reduces the risk of missed backups and gives you faster, more targeted recovery options when something goes wrong.
