Import a CSV With \copy

You have a CSV file and want it in a table. psql has a command for this, \copy, and it is faster and simpler than writing INSERT statements. It also has two traps: the file path, and what happens when one line is wrong. This page loads a small file, then shows the two errors you are most likely to meet.

Step 1: the CSV file

Create customers.csv with a header row and five customers.

name,email,city,joined
Ana Reyes,ana@example.com,Manila,2026-01-12
Ben Cruz,ben@example.com,Cebu,2026-02-03
Carla Diaz,carla@example.com,Manila,2026-02-20
Dan Lim,dan@example.com,,2026-03-15
Ella Tan,ella@example.com,Davao,2026-04-01

Dan Lim's city is left empty on purpose. By default, an empty field in a CSV becomes NULL.

If PostgreSQL runs in the Docker container from the install tutorial, copy the file into the container, because psql reads it from where psql runs.

docker cp customers.csv shop-pg:/tmp/customers.csv

Step 2: create a table that fits

Make the columns match the file, in the same order, with types that fit the values.

CREATE TABLE customers_import (
  name   text NOT NULL,
  email  text NOT NULL,
  city   text,
  joined date NOT NULL
);

CREATE TABLE

Step 3: load it with \copy

\copy customers_import FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER true)

COPY 5

FORMAT csv says how to read the file, and HEADER true skips the first line. COPY 5 means five rows were loaded. Check them.

\pset null '(null)'
SELECT * FROM customers_import ORDER BY joined;

Null display is "(null)".
    name    |       email       |  city  |   joined
------------+-------------------+--------+------------
 Ana Reyes  | ana@example.com   | Manila | 2026-01-12
 Ben Cruz   | ben@example.com   | Cebu   | 2026-02-03
 Carla Diaz | carla@example.com | Manila | 2026-02-20
 Dan Lim    | dan@example.com   | (null) | 2026-03-15
 Ella Tan   | ella@example.com  | Davao  | 2026-04-01
(5 rows)

The empty city became NULL. \copy must be written on one line, because it is a psql command and not SQL, and it takes no semicolon.

\copy or COPY?

There are two commands with almost the same name. COPY runs on the server and reads a file from the server's disk, which needs special rights and fails with a permission error for most users. \copy runs in psql and reads a file from your side, then sends the rows to the server. Use \copy unless you run psql on the same machine as the server and have the rights. In the Docker case, "your side" is inside the container, which is why you copied the file in.

Step 4: when a line is wrong

A single bad line stops the whole load, and nothing is inserted. Try a file with a short row.

name,email,city,joined
Fay Lo,fay@example.com,Manila

\copy customers_import FROM '/tmp/customers_short.csv' WITH (FORMAT csv, HEADER true)

ERROR:  missing data for column "joined"
CONTEXT:  COPY customers_import, line 2: "Fay Lo,fay@example.com,Manila"

The message names the line and the missing column. Fix the line and run it again. A value of the wrong type is reported the same way.

name,email,city,joined
Gus Ho,gus@example.com,Cebu,n/a

\copy customers_import FROM '/tmp/customers_date.csv' WITH (FORMAT csv, HEADER true)

ERROR:  invalid input syntax for type date: "n/a"
CONTEXT:  COPY customers_import, line 2, column joined: "n/a"

Check that the table still has five rows, since both failed loads were rolled back.

SELECT count(*) AS rows FROM customers_import;

 rows
------
    5
(1 row)

If your file uses semicolons or tabs, add DELIMITER ';' or DELIMITER E'\t' inside the WITH (...) list. If it has non-English letters that come out broken, add ENCODING 'UTF8', or the real encoding of the file.

Related course: SQL for Beginners.