Import CSV File into Table | PostgreSQL Tutorial

PostgreSQL provides two ways to import CSV data into a table: the server-side COPY statement and the client-side \copy psql command. COPY is faster because it reads the file directly on the server, but it requires the file to exist on the database host and the executing role to have superuser privileges. \copy streams the file from your local machine through the psql connection and works with ordinary user privileges.

Importing with the COPY statement

Create a target table first:

CREATE TABLE persons (
  id         SERIAL,
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  dob        DATE,
  email      VARCHAR(255),
  PRIMARY KEY (id)
);

Import a CSV file that has a header row and uses comma delimiters:

COPY persons (first_name, last_name, dob, email)
FROM '/var/lib/postgresql/persons.csv'
DELIMITER ','
CSV HEADER;

COPY 2

The COPY 2 output confirms two rows were imported. Key points about the syntax:

List the columns explicitly when the CSV does not include the id column (or any serial/identity column) so PostgreSQL can generate it automatically.
The HEADER keyword tells COPY to skip the first line of the file.
The file path must be readable by the OS user that runs the PostgreSQL server process.

Verify the imported data:

SELECT * FROM persons;

 id | first_name | last_name |    dob     |         email
----+------------+-----------+------------+-----------------------
  1 | John       | Doe       | 1995-01-05 | [email protected]
  2 | Jane       | Doe       | 1995-02-05 | [email protected]

Importing from a local machine with copy

When the CSV file lives on your workstation rather than the server, use the psql meta-command \copy. It does not require superuser rights and handles the file transfer transparently:

copy persons (first_name, last_name, dob, email)
FROM '/Users/you/persons.csv'
WITH (FORMAT csv, HEADER true)

\copy uses the same syntax as COPY but runs on the client side. The file path is resolved on your local machine, not the server.

Production tips for importing CSV data

Import into a staging table first, validate the data, then insert into the production table. This avoids leaving the production table in a partial state if the CSV contains errors.
Use TRUNCATE TABLE persons RESTART IDENTITY before a full reload so serial IDs restart from 1.
For large files, drop indexes and constraints before importing and rebuild them afterward — bulk loading without indexes is significantly faster.
If your CSV uses a different delimiter (tab, pipe), change the DELIMITER option: DELIMITER E'\t' for tab-separated files.
Handle encoding issues by adding ENCODING 'UTF8' (or the appropriate encoding) to the COPY options when source files are not in the database default encoding.

Frequently Asked Questions

What is the difference between COPY and \copy in PostgreSQL?

COPY is a server-side SQL command that reads the file directly on the PostgreSQL host and requires superuser privileges. \copy is a psql client-side command that reads the file from your local machine and streams it to the server, so it works without superuser rights.

How do I skip the header row when importing a CSV?

Add the HEADER keyword to the COPY command: COPY table FROM '/path/file.csv' DELIMITER ',' CSV HEADER. PostgreSQL will ignore the first line of the file.

Can I import only some columns from a CSV file?

Yes. List the columns you want to populate in parentheses after the table name: COPY persons (first_name, last_name, email) FROM '/path/file.csv' CSV HEADER. Columns not listed must have default values or be nullable.

What happens if a row in the CSV violates a constraint?

The entire COPY command fails and no rows are imported. To handle errors selectively, import into an unconstrained staging table first, clean the data, then insert validated rows into the target table.

How do I import a tab-separated file instead of comma-separated?

Change the DELIMITER option: COPY table FROM '/path/file.tsv' DELIMITER E'\t' CSV. You can also omit DELIMITER and use the default tab delimiter with the TEXT format.