How to Import a CSV Into PostgreSQL (COPY, \copy, and GUI Tools)

You have a spreadsheet — customers, products, last year's orders — and you want it in your Postgres database. Export it as CSV (comma-separated values) and load it in. Here's how.

Step 1: create the table

Postgres needs a table with matching columns first. For a customers.csv like:

email,name,signed_up,plan
ada@example.com,Ada Lovelace,2026-01-14,pro
grace@example.com,Grace Hopper,2026-02-03,

create:

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name text,
  signed_up date,
  plan text
);

Tip: when unsure of types, start with text columns in a temporary "staging" table, import, then clean up and copy into the real table with SQL. It's much easier than fighting import errors.

Option 1: \copy in psql (most common)

From your computer, connect with psql and run:

\copy customers(email, name, signed_up, plan) FROM 'customers.csv' WITH (FORMAT csv, HEADER true)

• \copy reads the file from your computer and sends it to the database — works with remote and managed databases.
• List the columns so the auto-generated id is filled in for you.
• HEADER true skips the first line.

(How to view your Postgres database)

Option 2: COPY (server-side)

COPY customers(email, name, signed_up, plan)
FROM '/var/lib/postgresql/import/customers.csv'
WITH (FORMAT csv, HEADER true);

COPY (no backslash) reads the file from the database server's disk, so the file must be there and readable by the Postgres process, and you need elevated privileges. It's the fastest option for very large files on your own server. On managed databases you usually can't use it — use \copy.

Option 3: a GUI tool

• pgAdmin: right-click the table → Import/Export Data.
• DBeaver: right-click the table → Import Data → CSV, with a column-mapping screen.
• TablePlus, DataGrip: similar import wizards.

Good for one-off imports and for seeing the mapping visually.

Option 4: your platform's dashboard

Supabase's table editor and several other hosted dashboards let you upload a CSV straight into a table. Fine for small files.

Common options

\copy t FROM 'file.csv' WITH (
  FORMAT csv,
  HEADER true,
  DELIMITER ';',        -- for semicolon-separated files (common from European Excel)
  NULL '',              -- treat empty fields as NULL
  ENCODING 'UTF8'
)

Errors you'll hit, and fixes

invalid input syntax for type date: "14/01/2026" The date isn't in a format Postgres expects. Use 2026-01-14 (ISO) in the spreadsheet, or import into a text staging column and convert with to_date(value, 'DD/MM/YYYY'). (Dates and time zones)

extra data after last expected column A value contains a comma that isn't quoted, or the delimiter is wrong. Make sure the file is a proper CSV export (values with commas are wrapped in quotes) and check DELIMITER.

duplicate key value violates unique constraint The file has duplicates, or rows already exist. Import into staging, then insert with ON CONFLICT DO NOTHING. (Fix it, Postgres upsert)

invalid byte sequence for encoding "UTF8" The file was saved in another encoding (often from Excel on Windows). Re-save as "CSV UTF-8".

permission denied / could not open file You used COPY for a file on your computer. Use \copy.

null value in column ... violates not-null constraint Some rows are missing a required value. Fix the data or relax the constraint in staging.

The staging pattern

CREATE TEMP TABLE staging (email text, name text, signed_up text, plan text);
\copy staging FROM 'customers.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO customers (email, name, signed_up, plan)
SELECT lower(trim(email)), trim(name), to_date(signed_up, 'YYYY-MM-DD'), NULLIF(plan, '')
FROM staging
ON CONFLICT (email) DO NOTHING;

Clean, convert and de-duplicate in SQL, where you can see and re-run each step.

Exporting to CSV

The reverse:

\copy (SELECT * FROM customers ORDER BY id) TO 'customers.csv' WITH (FORMAT csv, HEADER true)

---

EasySpawn gives your app its own Postgres database and a terminal on the same server — upload the CSV, and Claude Code can write the staging and clean-up SQL for you. See how it works or join the waitlist.

Related: What Is PostgreSQL? · How to View Your Postgres Database · Database Seeding Explained · SQL for Beginners