How to Import Data into PostgreSQL

At some point, almost every PostgreSQL project needs data from somewhere else — a CSV export from an old system, a JSON dump from an API, or a full dataset from another database entirely. I’ve done this migration dance more times than I can count, and I’ve learned that the difference between an import that takes five minutes and one that takes five frustrating hours usually comes down to picking the right tool and understanding a handful of gotchas up front. Let me walk you through the main approaches.

Overview: Your Import Options

PostgreSQL gives you several ways to get data in, depending on the source format and how much control you need:

• COPY — PostgreSQL’s native, fast bulk-loading command for CSV, text, and binary formats.
• \copy — the psql client-side equivalent of COPY, useful when the file lives on your local machine rather than the server.
• pg_restore / psql — for restoring from PostgreSQL dump files (covered in more depth in my article on restoring from backup).
• Foreign Data Wrappers (postgres_fdw, file_fdw) — for querying external data sources without a full import.
• Third-party ETL tools — for complex transformations, though I’ll focus on native PostgreSQL tools here since they cover the vast majority of real-world needs.

Importing CSV Data with COPY

COPY is the fastest way to load large amounts of data into PostgreSQL, but it runs on the server, which means the file needs to be accessible to the PostgreSQL server process, not just your local machine.

First, create the target table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary NUMERIC,
    hire_date DATE
);

Then import:

COPY employees (id, name, department, salary, hire_date)
FROM '/var/lib/postgresql/data/employees.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

Breaking down the options:

• FORMAT csv — tells PostgreSQL to parse the file as CSV.
• HEADER true — skips the first line, since it’s a header row rather than data.
• DELIMITER ',' — the field separator (change to '\t' for tab-separated files, for example).

This requires either superuser privileges or membership in the pg_read_server_files role, and the file must be readable by the PostgreSQL server’s OS user (usually postgres).

Importing CSV Data with \copy

If your file lives on your local machine and you’re connecting to a remote PostgreSQL server, COPY won’t work directly since it expects a server-side path. Instead, use \copy from within psql, which streams the file through your client connection:

\copy employees (id, name, department, salary, hire_date) FROM 'employees.csv' WITH (FORMAT csv, HEADER true)

This is functionally almost identical to COPY but doesn’t require special server-side file permissions, since it reads the file from wherever you’re running psql.

Handling Import Errors Gracefully

By default, COPY stops at the first row that fails to parse or violates a constraint. For large files, this can be painful — you fix one bad row, rerun, and hit the next one.

As of PostgreSQL 17, you can use the ON_ERROR option to skip bad rows instead of aborting:

COPY employees FROM '/data/employees.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore);

On earlier versions without this option, a common workaround is to first COPY into a staging table where every column is TEXT (so almost nothing fails to parse), then clean and cast the data as you move it into the real table:

CREATE TABLE employees_staging (
    id TEXT, name TEXT, department TEXT, salary TEXT, hire_date TEXT
);

COPY employees_staging FROM '/data/employees.csv' WITH (FORMAT csv, HEADER true);

INSERT INTO employees (id, name, department, salary, hire_date)
SELECT id::INT, name, department, salary::NUMERIC, hire_date::DATE
FROM employees_staging
WHERE id ~ '^\d+$'; -- basic filter to skip obviously malformed rows

Importing JSON Data

For a file containing a JSON array or JSON Lines format, you can load it as text first and then unpack it:

CREATE TABLE raw_json_import (data JSONB);

COPY raw_json_import (data) FROM '/data/employees.jsonl' WITH (FORMAT text);

Wait — for JSON Lines (one JSON object per line), it’s actually cleaner to treat each line as its own JSONB value:

CREATE TABLE raw_json_import (data JSONB);

\copy raw_json_import (data) FROM 'employees.jsonl'

Then extract fields into a proper structured table:

INSERT INTO employees (id, name, department, salary)
SELECT
    (data->>'id')::INT,
    data->>'name',
    data->>'department',
    (data->>'salary')::NUMERIC
FROM raw_json_import;

Importing Data from Another PostgreSQL Database

If you’re moving data between two PostgreSQL databases, pg_dump combined with pg_restore (or piping directly) is usually the cleanest path:

pg_dump -h source_host -U source_user -d source_db -t employees | psql -h target_host -U target_user -d target_db

For a full walkthrough of dump and restore options, see my dedicated articles on backing up and restoring PostgreSQL databases.

Importing via Foreign Data Wrappers

If you need to query external data without fully importing it — or want a repeatable pipeline that pulls fresh data on demand — postgres_fdw lets you treat a table on another PostgreSQL server as if it were local:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER source_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'source_host', dbname 'source_db', port '5432');

CREATE USER MAPPING FOR CURRENT_USER
SERVER source_server
OPTIONS (user 'source_user', password 'source_password');

IMPORT FOREIGN SCHEMA public LIMIT TO (employees)
FROM SERVER source_server INTO public;

Once imported, you can query the foreign table directly, or pull it into a local table:

CREATE TABLE employees_local AS SELECT * FROM employees;

file_fdw works similarly for CSV files, letting you query a CSV as if it were a table without importing it first — useful for one-off exploration before deciding how to structure the real import.

Verifying the Import

After any import, I always run a few sanity checks:

SELECT COUNT(*) FROM employees;
SELECT * FROM employees LIMIT 10;
SELECT COUNT(*) FROM employees WHERE salary IS NULL;

Comparing row counts against the source file (wc -l employees.csv, keeping in mind the header row) is a quick way to catch silent truncation.

Common Use Cases

• Migrating data from a legacy system or spreadsheet exports into a production PostgreSQL database.
• Loading data warehouse feeds or third-party API exports for analytics.
• Bulk-seeding a database with test or reference data during development.
• Periodically syncing data from another PostgreSQL instance using foreign data wrappers.
• One-off data exploration on a CSV without committing to a full schema first.

Troubleshooting Common Issues

“permission denied for file” — the PostgreSQL server process (usually running as the postgres OS user) can’t read the file. Either move the file to a location it can access, adjust permissions, or switch to \copy, which doesn’t have this restriction.

“invalid input syntax for type X” — a value in the file doesn’t match the target column’s type. Load into a TEXT-typed staging table first, then cast and clean the data as you move it into the final table.

Extra whitespace or encoding issues — check the file’s encoding (file -i filename.csv) and specify it explicitly if needed:

COPY employees FROM '/data/employees.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

Duplicate key violations during import — if you’re re-importing data that might already exist, load into a staging table and use INSERT ... ON CONFLICT DO NOTHING (or DO UPDATE) rather than inserting directly into a table with a primary key constraint.

Import is unexpectedly slow — check whether you have unnecessary indexes or triggers active on the target table during a bulk load. Dropping non-essential indexes before import and rebuilding them afterward is often significantly faster than maintaining them row by row during the load.

Best Practices

• Use a staging table with permissive (TEXT) column types for messy or untrusted data, and cast/clean as a second step.
• Prefer \copy over COPY when working with remote servers or when you don’t have server filesystem access — it avoids a whole category of permission issues.
• Wrap large imports in a transaction so you can roll back cleanly if something goes wrong partway through.
• Temporarily drop non-critical indexes and constraints before very large bulk loads, then recreate them afterward for better performance.
• Always verify row counts and spot-check a sample of the data after importing — don’t assume a lack of errors means a fully correct import.
• For recurring imports from another system, consider postgres_fdw or a scheduled ETL process rather than manual one-off COPY commands.

Wrapping Up

Getting data into PostgreSQL efficiently comes down to picking the right tool for your source format and situation: COPY or \copy for CSV and text files, pg_dump/pg_restore for PostgreSQL-to-PostgreSQL transfers, and foreign data wrappers when you need a live connection rather than a one-time load. Whichever path you take, treat the staging-table-then-clean pattern as your safety net for messy real-world data — it’ll save you from a lot of failed imports and half-loaded tables.