SQL COPY

What is the SQL COPY command?

COPY moves data between a database table and an external file, program, or standard input-output for high-speed bulk import or export.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COPY: PostgreSQL (native), Amazon Redshift (similar), Greenplum (same syntax), EDB Advanced Server. Other systems use different keywords (e.g., MySQL LOAD DATA, SQL Server BULK INSERT, Snowflake COPY INTO).

SQL COPY Full Explanation

COPY is PostgreSQL’s high-performance bulk data loader and dumper. It can read data from a file, the operating-system program output, or the client’s standard input (STDIN) and insert it directly into a table, or write table/query results to a file, program, or standard output (STDOUT). COPY bypasses much of the SQL executor overhead by streaming rows straight to or from the storage layer, making it dramatically faster than many single-row INSERT or SELECT operations.Key behaviors- Operates in a single transaction by default; failure rolls back the whole operation.- Accepts many format options: CSV, TEXT, BINARY. CSV handling supports custom delimiters, quote, escape, header rows, and NULL representations.- Supports column lists so you can load/export subsets of columns.- Works with queries via COPY (SELECT ...) TO allowing fast ad-hoc exports.- Requires superuser or table ownership when accessing server-side files. Client-side use requires the psql meta-command \copy or STDIN/STDOUT.Caveats- Constraints and triggers fire normally; disable them or load into a staging table for maximum speed.- COPY cannot reference foreign tables or views directly (wrap in a SELECT).- Server-side paths are resolved relative to the database server, not the client machine.

SQL COPY Syntax

-- Import
COPY [table_name [ (column_list) ] ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ WITH (FORMAT { csv | text | binary }, DELIMITER 'char', NULL 'str', HEADER boolean, QUOTE 'char', ESCAPE 'char', ENCODING 'name' ... ) ];

-- Export table or query
COPY { table_name [ (column_list) ] | (SELECT query) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ WITH (FORMAT { csv | text | binary }, DELIMITER 'char', NULL 'str', HEADER boolean, QUOTE 'char', ESCAPE 'char', ENCODING 'name' ... ) ];

SQL COPY Parameters

table_name (identifier) - Target or source table.
column_list (list) - Optional subset of columns.
FROM / TO (keyword) - Direction of data movement.
filename (text) - Absolute or relative server-side path.
PROGRAM (text) - Shell command executed by server.
STDIN / STDOUT (keyword) - Use client’s standard streams.
FORMAT (enum) - csv, text, or binary.
DELIMITER (char) - Field separator (default TAB for text, comma for csv).
NULL (text) - String that represents NULL.
HEADER (boolean) - Include header line when FORMAT csv.
QUOTE (char) - Quoting character for csv (default ").
ESCAPE (char) - Escape character for csv (default ").
ENCODING (name) - Character set of the file.

Example Queries Using SQL COPY

-- 1. Import a CSV file into a staging table
COPY staging_orders
FROM '/var/lib/postgresql/imports/orders_2024_01.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL '', ENCODING 'UTF8');

-- 2. Export a table to a pipe-delimited text file
COPY public.customers TO '/tmp/customers.txt'
WITH (FORMAT text, DELIMITER '|');

-- 3. Export a query result to the client via STDOUT (psql > will capture)
COPY (
  SELECT id, email, created_at
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
) TO STDOUT WITH (FORMAT csv, HEADER true);

-- 4. Import from the client machine using psql meta-command
\copy products (sku, name, price) FROM '~/data/products.csv' CSV HEADER;

Expected Output Using SQL COPY

Rows are inserted into or written out of the specified table/query in bulk
Success returns COPY N where N is the number of rows processed
On error, the entire COPY is rolled back unless run inside a surrounding transaction block with error handling

Use Cases with SQL COPY

Seeding databases from large CSV extracts during initial loads.
Nightly batch imports of clickstream or log data.
Fast exports for data science notebooks or downstream systems.
Moving data between PostgreSQL servers through COPY TO + SCP + COPY FROM.
Generating lightweight, one-off CSV reports without using a BI tool.

Common Mistakes with SQL COPY

Supplying a client-side file path without using \copy; the server cannot access the client’s filesystem.
Forgetting HEADER true when your CSV file’s first line contains column names, causing misaligned data.
Using wrong DELIMITER or QUOTE characters leading to split/merged columns.
Expecting COPY to ignore constraints; UNIQUE or FOREIGN KEY violations will abort the copy.
Assuming partial success; COPY is atomic unless run in single-row error handling mode (PostgreSQL 12+).

First Introduced In

PostgreSQL 6.0 (1997)

Frequently Asked Questions

What privileges are needed to run COPY?

You must be a superuser or the table owner when reading or writing server-side files. Regular users can still import/export using the client-side \copy command.

Does COPY fire triggers and constraints?

Yes. All CHECK constraints, UNIQUE constraints, foreign keys, and row-level triggers execute as if the rows were inserted normally.

How can I ignore bad rows but keep good ones?

From PostgreSQL 12 onward, add the option ON_ERROR = skip or run COPY in a PL/pgSQL loop catching exceptions. Older versions require loading into a staging table and cleaning errors later.

How do I make COPY even faster?

Disable WAL (UNLOGGED tables), drop indexes and constraints before loading, increase maintenance_work_mem, and use BINARY format when both source and target are PostgreSQL.