Import CSV into PostgreSQL with COPY and \copy

Import a local CSV with psql \copy

create table customer_import (
  email text,
  full_name text,
  joined_on date,
  lifetime_value numeric(12, 2)
);

\copy customer_import (email, full_name, joined_on, lifetime_value) from '/Users/me/Downloads/customers.csv' with (format csv, header true, encoding 'UTF8')

\copy is a psql meta-command, so run it in psql rather than sending it as SQL through a generic query editor.

Choose COPY or \copy from the file location

COPY FROM '/path/file.csv' asks the PostgreSQL server process to read that path. On a hosted database, your Mac path does not exist on the server. Server-side file access also requires elevated privileges.

psql \copy uses COPY FROM STDIN and reads the file through the psql client. The database role still needs INSERT on the target table, but the file only needs to be readable by your local psql process.

Import into a staging table first

A staging table keeps file parsing separate from application constraints and transformations. Start with types that faithfully accept the source, inspect the rows, and then insert cleaned values into the final table.

Do not make every staging column text by habit when the source contract is reliable. Types such as date and numeric expose bad input early. Use text where normalization is genuinely required.

Be explicit about NULL and empty strings

In PostgreSQL CSV format, an unquoted empty field represents NULL by default. A quoted empty field represents an empty string. If the source writes a marker such as NULL or N/A, state it with the NULL option only when that marker cannot be legitimate data.

Dates, decimal separators, embedded newlines, quotes, byte-order marks, and inconsistent column counts are common failures. Inspect the source file as data, not just as a spreadsheet preview.

Import a source that uses NULL as its null marker

\copy customer_import from '/Users/me/Downloads/customers.csv' with (format csv, header true, null 'NULL', encoding 'UTF8')

Validate before merging

Count the staged rows, test required fields, find duplicate keys, and inspect values that fail the destination rules. Keep these checks next to the import command so another run uses the same definition of valid data.

Run basic staging checks

select count(*) from customer_import;

select * from customer_import
where email is null or btrim(email) = '';

select lower(email), count(*)
from customer_import
group by lower(email)
having count(*) > 1;

Move valid rows in one reviewed statement

Insert from staging into the destination with explicit columns and transformations. Use a transaction when the final merge must be all-or-nothing. For a large load, consider lock duration, WAL volume, indexes, triggers, and available disk space before wrapping everything in one transaction.

After the import, browse the staging and destination tables in PostgresGUI and run spot checks on the same connection. The PostgreSQL column type guide can help when source values do not map cleanly to the destination schema.

Normalize and merge valid rows

begin;

insert into customers (email, full_name, joined_on, lifetime_value)
select
  lower(btrim(email)),
  nullif(btrim(full_name), ''),
  joined_on,
  lifetime_value
from customer_import
where email is not null and btrim(email) <> '';

commit;