Import CSV into PostgreSQL with COPY, \copy or a GUI

COPY ... FROM loads a file into a table that already exists. The examples use a products table with id serial PRIMARY KEY, name, sku (unique), price, category and stock_quantity, and a CSV whose header is name,sku,price,category,stock_quantity. List the columns after the table name so the id is generated for you.

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products.csv'
WITH (FORMAT csv, HEADER);

Every statement below was run on PostgreSQL 16 and 18. Where an option needs a newer release, the section says which one.

COPY vs \copy

COPY runs on the database server and opens the file there. \copy is a psql command that reads the file on your own computer and streams it to the server, so it is the one to use for a remote database, a Docker container or a managed service such as RDS.

\copy products (name, sku, price, category, stock_quantity) FROM 'products.csv' WITH (FORMAT csv, HEADER)

Point COPY at a file that only exists on your laptop and the server cannot find it. The hint tells you to switch:

ERROR:  could not open file "/tmp/client_only.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

Relative paths in COPY are resolved from the server's data directory, not from your shell, so COPY products FROM 'products.csv' fails the same way. With \copy, a relative path is relative to the folder you started psql in.

There are two different permission errors. The first means your role may not read server files at all:

ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Use \copy, or have a superuser run GRANT pg_read_server_files TO app;. The second comes from the operating system: the role is allowed, but the postgres user cannot read the file or its folder (a home directory, /root, or a file with mode 600).

ERROR:  could not open file "/root/tutdata/products.csv" for reading: Permission denied

Move the file somewhere the server can read, such as /tmp, fix its permissions, or use \copy, which reads the file as you.

Example: import from the shell in one line, or feed the file to COPY through standard input.

psql -d shop -c "\copy products (name, sku, price, category, stock_quantity) FROM 'products.csv' WITH (FORMAT csv, HEADER)"

psql -d shop -c "COPY products (name, sku, price, category, stock_quantity) FROM STDIN WITH (FORMAT csv, HEADER)" < products.csv

With and without a header

HEADER skips the first line. COPY does not read the names in it; it fills the listed columns from left to right. Forget HEADER and the column names are parsed as data:

ERROR:  invalid input syntax for type numeric: "price"
CONTEXT:  COPY products, line 1, column price: "price"

For a file without a header, leave the option out. HEADER MATCH (PostgreSQL 15 and later) checks that the header names match the column list in order, which catches a file whose columns were rearranged:

COPY products (sku, name, price, category, stock_quantity)
FROM '/tmp/products.csv' WITH (FORMAT csv, HEADER MATCH);

ERROR:  column name mismatch in header line field 1: got "name", expected "sku"

Leaving out the column list makes COPY expect every table column in order, starting with id, so the first name lands in the integer column: invalid input syntax for type integer: "Walnut Desk Lamp".

Delimiters: tab and semicolon

DELIMITER takes one character. Without it, a semicolon file is read as one long field per line and fails with missing data for column "price".

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products_semicolon.csv' WITH (FORMAT csv, HEADER, DELIMITER ';');

Files from European spreadsheets often pair the semicolon with a decimal comma, which numeric columns reject: invalid input syntax for type numeric: "49,90". Load the column as text into a staging table and convert it with replace(price, ',', '.')::numeric.

For tab-separated files, use DELIMITER E'\t' with FORMAT csv, or the default text format, which expects tabs and writes NULL as \N:

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products.tsv' WITH (FORMAT text, HEADER);

NULL values and empty strings

In CSV format, an unquoted empty field is NULL and a quoted empty field ("") is an empty string. The word NULL in the file is just text unless you say otherwise.

Example: treat empty fields, quoted or not, as NULL in the category column.

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products.csv' WITH (FORMAT csv, HEADER, FORCE_NULL (category));

FORCE_NULL * and FORCE_NOT_NULL * for every column need PostgreSQL 17. On 16 the star is refused with argument to option "force_null" must be a list of column names.

An empty field does not fall back to the column default. It is NULL, so a NOT NULL column with a default still fails:

ERROR:  null value in column "stock_quantity" of relation "products" violates not-null constraint

Either leave the column out of the column list, so every row gets the default, or mark the cells with a placeholder and the DEFAULT option. DEFAULT '' is not allowed in CSV mode because it clashes with the NULL marker (NULL specification and DEFAULT specification cannot be the same).

Quotes and commas inside values

CSV format already handles double-quoted values. "Chair, Oak" stays one field and "The ""Pro"" Keyboard" loads as The "Pro" Keyboard. Change QUOTE when the file uses another character, and ESCAPE when quotes inside values are escaped with a backslash instead of doubled.

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products_single_quote.csv' WITH (FORMAT csv, HEADER, QUOTE '''');

FORMAT text has no quoting at all, so do not use it for a file with quoted fields.

Dates and timestamps

COPY parses dates with the session's DateStyle, which is ISO, MDY by default. A day-first date such as 25/12/2024 fails, and worse, 05/03/2024 loads quietly as May 3.

ERROR:  date/time field value out of range: "25/12/2024"
HINT:  Perhaps you need a different "DateStyle" setting.

Set the order for the session before the load:

SET datestyle = 'ISO, DMY';
COPY products_launches (sku, launched_on, updated_at)
FROM '/tmp/dates.csv' WITH (FORMAT csv, HEADER);

For any other layout, load the column as text and convert it with to_date(launched_on, 'DD/MM/YYYY') or to_timestamp(updated_at, 'DD/MM/YYYY HH24:MI').

Encoding

COPY assumes the file uses the client encoding, which is usually UTF-8. A Latin-1 or Windows-1252 export fails on the first accented letter:

ERROR:  invalid byte sequence for encoding "UTF8": 0xe9 0x20 0x54

Name the file's encoding and PostgreSQL converts it on the way in:

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products_latin1.csv' WITH (FORMAT csv, HEADER, ENCODING 'LATIN1');

Use ENCODING 'WIN1252' for files saved by older versions of Excel on Windows.

Import into a new table or a temp table

COPY never creates a table. Write the CREATE TABLE first, with one column per CSV column in the same order.

CREATE TABLE products_import (
  name text,
  sku text,
  price numeric(10,2),
  category text,
  stock_quantity integer
);
\copy products_import FROM 'products.csv' WITH (FORMAT csv, HEADER)

CREATE TABLE products_import (LIKE products INCLUDING DEFAULTS) copies an existing table's columns and defaults without its indexes. For a one-off check, use CREATE TEMP TABLE; it disappears when the session ends, or at COMMIT with ON COMMIT DROP.

Import specific columns or skip columns

List the target columns after the table name, in the order they appear in the file. Columns you leave out, such as id, get their defaults.

\copy products (name, sku, price) FROM 'prices.csv' WITH (FORMAT csv, HEADER)

COPY has no option to skip a column in the file. If the file has more columns than the list, it fails with extra data after last expected column. Load the full file into an all-text staging table and pick the columns you want:

CREATE TEMP TABLE products_raw (
  id text, name text, sku text, price text,
  category text, internal_note text, stock_quantity text
);
\copy products_raw FROM 'products_extra.csv' WITH (FORMAT csv, HEADER)

INSERT INTO products (name, sku, price, category, stock_quantity)
SELECT name, sku, price::numeric, category, stock_quantity::int
FROM products_raw;

On macOS or Linux, \copy ... FROM PROGRAM can cut the columns out before they reach the server. It is fine for simple files, but cut splits on every comma, including commas inside quoted values.

\copy products (name, sku, price, category, stock_quantity) FROM PROGRAM 'cut -d, -f2-5,7 products_extra.csv' WITH (FORMAT csv, HEADER)

WHERE filters rows during the load (PostgreSQL 12 and later), for example \copy products_import FROM 'products.csv' WITH (FORMAT csv, HEADER) WHERE category = 'Home'.

Skip bad rows with ON_ERROR

By default one bad value stops the whole COPY and nothing is kept. PostgreSQL 17 added ON_ERROR ignore, which skips rows whose values cannot be converted to the column type:

COPY products (name, sku, price, category, stock_quantity)
FROM '/tmp/products_bad.csv'
WITH (FORMAT csv, HEADER, ON_ERROR ignore, LOG_VERBOSITY verbose);

NOTICE:  skipping row due to data type incompatibility at line 3 for column "price": "ten"
NOTICE:  1 row was skipped due to data type incompatibility
COPY 2

PostgreSQL 18 adds REJECT_LIMIT n, which still fails the COPY once more than n rows have been skipped. Version differences found in testing:

• PostgreSQL 16: option "on_error" not recognized.
• PostgreSQL 17: ON_ERROR and LOG_VERBOSITY work; option "reject_limit" not recognized.
• PostgreSQL 18: all three work. ON_ERROR set_null is refused with COPY ON_ERROR "set_null" not recognized.

ON_ERROR only covers type conversion. A row with too few fields (missing data for column "category"), a NOT NULL or CHECK failure, or a duplicate key still stops the load. For those, or on PostgreSQL 16, load into an all-text staging table and filter with SQL.

Duplicates and upserts

COPY has no ON CONFLICT clause. A row that repeats a unique key fails the load:

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(SKU-HOM-WDL001) already exists.

Load into a staging table first, then insert from it with ON CONFLICT. DO NOTHING skips rows that already exist; DO UPDATE turns the import into an upsert.

Example: update prices and stock for existing SKUs and add the new ones, all in one transaction.

BEGIN;
CREATE TEMP TABLE products_stage (LIKE products INCLUDING DEFAULTS) ON COMMIT DROP;
\copy products_stage (name, sku, price, category, stock_quantity) FROM 'products_update.csv' WITH (FORMAT csv, HEADER)

INSERT INTO products (name, sku, price, category, stock_quantity)
SELECT DISTINCT ON (sku) name, sku, price, category, stock_quantity
FROM products_stage
ORDER BY sku
ON CONFLICT (sku) DO UPDATE
  SET price = EXCLUDED.price,
      stock_quantity = EXCLUDED.stock_quantity;
COMMIT;

DISTINCT ON matters when the file itself repeats a key. Without it, the upsert fails with ON CONFLICT DO UPDATE command cannot affect row a second time. Add a column to the ORDER BY (a line number or an updated_at) to choose which duplicate wins.

Import a large CSV file

COPY is already the fastest way in. In testing, \copy loaded 2 million rows (79 MB) in under a second on both versions. For files in the tens of gigabytes:

• Load into a table without indexes or foreign keys, then add them after. One index build at the end is much faster than updating it for every row.
• FREEZE writes rows already frozen, which saves a later VACUUM pass. It only works when the table was created or truncated in the same transaction; otherwise you get cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction.
• An UNLOGGED staging table skips the write-ahead log. Its contents are lost after a crash, so copy the rows into the real table once they are clean.
• Raise maintenance_work_mem for the session before CREATE INDEX on the loaded table.

Example: load a fresh table with FREEZE.

BEGIN;
TRUNCATE products_import;
COPY products_import FROM '/tmp/products.csv' WITH (FORMAT csv, HEADER, FREEZE);
COMMIT;

Watch a running COPY from a second session (PostgreSQL 14 and later). With \copy the type is PIPE and bytes_total is 0, because the server cannot see the file size.

SELECT relid::regclass, type, bytes_processed, bytes_total, tuples_processed
FROM pg_stat_progress_copy;

Import a SQL file or dump

A plain .sql file, including a default pg_dump dump, runs with psql -f. A custom-format dump (pg_dump -Fc) or a directory dump needs pg_restore. Each tool tells you when you picked the wrong one:

The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

pg_restore: error: input file appears to be a text format dump. Please use psql.

Example: restore a plain dump so that it stops at the first error and keeps nothing if it fails, then restore a custom dump over existing tables.

psql -d shop -v ON_ERROR_STOP=1 --single-transaction -f shop.sql

pg_restore -d shop --clean --if-exists --no-owner shop.dump

psql's default is to report an error and carry on with the next statement, which can leave a half-restored database. --no-owner skips the ALTER ... OWNER statements, which fail when the original owner role does not exist on the new server.

Dumps made with pg_dump 17 or 18 start with SET transaction_timeout = 0;. Loading one into PostgreSQL 16 or older prints unrecognized configuration parameter "transaction_timeout". Without ON_ERROR_STOP, psql and pg_restore skip that line and the rest loads; with ON_ERROR_STOP, psql stops right there, so delete the line from a plain dump first. Version checks run the other way too: pg_restore 16 cannot read a custom dump from pg_dump 18 (unsupported version (1.16) in file header), and pg_dump refuses a newer server (aborting because of server version mismatch). Use the client tools from the newest version involved.

Import JSON

COPY has no JSON input format, so load each document into a jsonb column and expand it with SQL. For NDJSON (one object per line), read the file as single-column CSV with quote and delimiter characters that never appear in JSON. The default text format would treat backslashes as escapes and fail with Escape sequence "\s" is invalid.

CREATE TEMP TABLE products_json (doc jsonb);
\copy products_json FROM 'products.ndjson' WITH (FORMAT csv, QUOTE E'\x01', DELIMITER E'\x02')

INSERT INTO products (name, sku, price, category, stock_quantity)
SELECT r.name, r.sku, r.price, r.category, r.stock_quantity
FROM products_json, jsonb_populate_record(NULL::products, doc) AS r;

For a file holding one JSON array, read it whole and expand it with jsonb_populate_recordset. pg_read_file reads on the server and needs superuser or pg_read_server_files; from psql, a variable reads it on your machine.

\set content `cat products.json`
INSERT INTO products (name, sku, price, category, stock_quantity)
SELECT name, sku, price, category, stock_quantity
FROM jsonb_populate_recordset(NULL::products, :'content'::jsonb);

On PostgreSQL 17 and later, JSON_TABLE maps nested paths to columns, for example SELECT * FROM JSON_TABLE(:'content'::jsonb, '$[*]' COLUMNS (name text PATH '$.name', price numeric PATH '$.price')). PostgreSQL 16 does not have it and reports a syntax error at COLUMNS.