PostgreSQL CSV import: COPY, \copy, and common errors

To import a CSV file into PostgreSQL from your computer, use \copy in psql with an explicit column list and CSV options. PostgreSQL validates the incoming values against the destination table’s types and constraints. Verify the imported rows before treating the job as complete.

This walkthrough uses four original records and PostgreSQL 18.6. We ran 31 checks covering real file imports, quoted commas, embedded newlines, UTF-8 text, NULL values, header behavior, failed imports, transaction rollback, and permissions. The tests used a disposable local database; they do not measure bulk-load throughput.

Choose where the CSV file will be read

The psql reference describes \copy as a client operation. The psql process opens the local file and sends its contents through the database connection. SQL COPY ... FROM 'filename' asks the database server to open the file instead.

If psql runs on your laptop and PostgreSQL runs elsewhere, a laptop path belongs in \copy. If psql runs inside a container, its accessible filesystem determines the path. Our PostgreSQL with Docker Compose guide covers a separate local database setup.

These are different file-access paths. In our lab, a restricted database role could import through \copy with schema access and INSERT privileges on the imported columns. It could not issue server-file COPY. Revoking its INSERT privilege also made \copy fail. Client-side file access does not bypass database permissions.

Prepare a CSV with cases you can verify

Save this as orders.csv in UTF-8. Keep the line break inside the third record’s quoted note. There are four data records even though one spans two physical lines.

name,id,note,amount
"North, hub",1,,12.50
"Quote ""desk""",2,"",0.00
Café,3,"line one
line two",7.25
Plain,4,NULL,1.25

The file order is name, id, note, amount. The table below deliberately uses a different physical order, starting with id. The import command supplies the correct mapping by position.

Work in a disposable database named csv_demo, with a role allowed to create the exercise schema. It must not already contain csv_lab. Save the next block as 01-import.sql alongside the CSV. From that directory, run psql -X -qAt -P 'null=<NULL>' -v ON_ERROR_STOP=1 -d csv_demo -f 01-import.sql, adding the host and user options your connection needs.

The output below uses psql’s unaligned, tuples-only format and displays SQL NULL as <NULL>. In an ordinary interactive session, table borders and command tags may also appear. Keep each \copy command on one physical line; it is a psql command, so a generic SQL-only editor cannot execute it as SQL.

Import with an explicit column list

BEGIN;
CREATE SCHEMA csv_lab;
CREATE TABLE csv_lab.orders (
    id integer PRIMARY KEY,
    name text NOT NULL,
    note text,
    amount numeric(10,2) NOT NULL,
    source text NOT NULL DEFAULT 'csv'
);
\copy csv_lab.orders (name, id, note, amount) FROM 'orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')
SELECT count(*) AS rows, sum(amount) AS total FROM csv_lab.orders;
COMMIT;

4|21.00

The result is four rows totaling 21.00. The imported column list excludes source, so each record receives that column’s default value. The transaction groups creation and loading; ON_ERROR_STOP makes the script stop if an operation fails.

The COPY reference documents the format and column options. Here, FORMAT csv selects CSV parsing, HEADER true skips the first record, and ENCODING specifies the file’s UTF-8 encoding. The path is relative to psql’s working directory, not automatically to the script file’s directory.

For another file, inspect the separator, encoding, header, quote rules, and destination types first. Do not repair a quoted CSV by splitting every line on commas: the first record contains a comma inside its name, and the third contains a newline inside its note.

Check NULL, empty strings, quotes, and exact values

Run 02-inspect.sql with the same psql options:

SELECT id, name, note IS NULL AS note_is_null,
       length(note) AS note_length, amount, source
FROM csv_lab.orders ORDER BY id;
SELECT id, replace(note, chr(10), '<LF>') AS visible_note
FROM csv_lab.orders WHERE id IN (3, 4) ORDER BY id;

1|North, hub|t|<NULL>|12.50|csv
2|Quote "desk"|f|0|0.00|csv
3|Café|f|17|7.25|csv
4|Plain|f|4|1.25|csv
3|line one<LF>line two
4|NULL

Record 1 has an unquoted empty note, which becomes SQL NULL with these CSV settings. Record 2 has a quoted empty note, which becomes a non-NULL string of length zero. Record 4 contains the literal text NULL and retains four characters. The word NULL is not a special marker in this file.

The comma in North, hub remains part of the name. The doubled quotes in the second name become one pair of literal quotes. Café retains its accented character. The third note has length 17 and includes a newline; the second query replaces that newline with <LF> only for display.

These checks are more informative than a row count alone. A successful conversion can still place valid text in the wrong column. Check known identifiers and representative values, including empty fields and quoted records.

Observe a failed import before retrying

Save this second file as bad-amount.csv. It begins with a valid new row, followed by an invalid numeric value and another valid row.

name,id,note,amount
Valid first,5,ok,2.00
Invalid second,6,ok,not-a-number
Valid last,7,ok,3.00

Run the following as a separate script, 03-type-error.sql, with ON_ERROR_STOP still enabled:

\copy csv_lab.orders (name, id, note, amount) FROM 'bad-amount.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

Our run failed with SQLSTATE 22P02, reporting invalid numeric input for not-a-number. It did not continue to the last record. In a new invocation, run 04-check-after-error.sql:

SELECT count(*) AS rows, sum(amount) AS total FROM csv_lab.orders;
SELECT count(*) AS new_rows FROM csv_lab.orders WHERE id IN (5, 6, 7);

4|21.00
0

The original four rows and total remain unchanged. None of IDs 5, 6, or 7 is visible, including the valid row encountered before the error. Separate tests also rejected extra fields, an incorrect column order, and a duplicate primary key. The valid row before the duplicate-key error was rolled back too.

If you run an import inside a larger explicit transaction, its failure affects that transaction. Our additional test inserted ID 99, attempted the bad import, and exited psql after the error; the earlier INSERT was rolled back. In an interactive session, issue ROLLBACK before starting again after a transaction error.

Inspect the reported column and offending record, correct the source, and verify the destination before retrying. Do not assume rerunning a successful import is harmless: COPY adds rows, and repeated keys can fail. This guide keeps the default stop-on-error behavior rather than discarding malformed records.

Do not treat HEADER true as name matching

Save swapped-header.csv with these two columns:

second,first
B,A

The following script, 05-header-true.sql, imports into two text columns in the opposite order:

CREATE TABLE csv_lab.header_probe (first text, second text);
\copy csv_lab.header_probe (first, second) FROM 'swapped-header.csv' WITH (FORMAT csv, HEADER true)
SELECT first, second FROM csv_lab.header_probe;

B|A

The result is first=B and second=A. HEADER true skipped the names; it did not rearrange the values. Because both fields are text, conversion could not expose the mistake.

In the tested PostgreSQL 18 version, HEADER MATCH checks the header against the ordered import columns. This separate 06-header-match.sql fails with SQLSTATE 22P04:

\copy csv_lab.header_probe (first, second) FROM 'swapped-header.csv' WITH (FORMAT csv, HEADER MATCH)

The existing header_probe row remained unchanged. A separate import with the original orders.csv header and matching explicit column order succeeded. HEADER MATCH validates the order; it does not reorder fields. Check support in your PostgreSQL version before using the option.

Remove the exercise objects

After running the examples, 07-cleanup.sql removes only the tables and schema created for this walkthrough:

DROP TABLE csv_lab.header_probe;
DROP TABLE csv_lab.orders;
DROP SCHEMA csv_lab;

Our harness also removed its test role, stopped the isolated server, and deleted its temporary password file. The downloadable package contains the CSV fixtures, SQL scripts, captured results, and validation harness, without a database directory or credentials.

For a real import, record the source file, column mapping, accepted row count, and validation results. CSV carries tabular values; it does not reproduce a database’s complete structure and permissions. Use our PostgreSQL backup and restore guide when recovery of a database is the task.