How to Import CSV into PostgreSQL: COPY Command, pgAdmin, and Automated Pipelines

Importing CSV data into PostgreSQL is one of the most common data engineering tasks, and also one of the most error-prone. Missing headers, encoding mismatches, date format inconsistencies, and NULL handling quirks cause more failed imports than any configuration mistake.

This guide covers three methods: Estuary for automated continuous ingestion, the native COPY command for fast one-time loads, and pgAdmin for GUI-based imports. Each section includes the exact commands, parameters, and error fixes you need to complete the import without data loss.

COPY vs. INSERT vs. \copy: Which to Use

Before choosing a method, understand the three core import mechanisms in PostgreSQL and when each applies.

Key distinction between COPY and \\copy:

COPY is a PostgreSQL server command. The file path you provide must be accessible from the database server's filesystem, not your local machine. If you run COPY with a local file path, it will fail with ERROR: could not open file.

\\copy is a psql meta-command that reads from your client machine and streams data to the server. Use \\copy whenever the CSV is on your laptop or workstation.

Prerequisites: Setting Up the Target PostgreSQL Table

PostgreSQL cannot infer schema from a CSV file on its own. The target table must exist before you run COPY. This is the step most guides skip, and it is where most imports fail.

Step 1: Check your CSV structure

Before writing any DDL, inspect the file:

bash
# Check encoding
file -i your_file.csv

# Preview first 5 rows
head -5 your_file.csv

# Count columns in header row
head -1 your_file.csv | tr ',' '\\n' | wc -l

Step 2: Map CSV columns to PostgreSQL types

Use this reference for common CSV field patterns:

Step 3: Create the target table

sql
-- Example: student records CSV
CREATE TABLE student_info (
  student_id   SERIAL PRIMARY KEY,
  first_name   VARCHAR(50)  NOT NULL,
  last_name    VARCHAR(50)  NOT NULL,
  dob          DATE,
  city         VARCHAR(50),
  created_at   TIMESTAMP    DEFAULT NOW()
);

If your CSV has an ID column you want to import directly (not auto-generate), use INTEGER instead of SERIAL and omit the DEFAULT.

Method 1: Automated CSV to PostgreSQL with Estuary

Best for: CSVs hosted at a URL, Google Sheets exports, or any CSV that updates regularly and needs continuous sync rather than a one-time import.

Estuary connects to a CSV source via HTTP endpoint or Google Sheets, infers the schema automatically, and materializes the data into PostgreSQL continuously. When the source CSV updates, PostgreSQL reflects the change without any manual re-import.

When to use Estuary over COPY

• The CSV is hosted online (S3, Google Sheets, HTTP endpoint) rather than on disk
• The file is updated regularly and PostgreSQL needs to stay in sync
• You want schema inference instead of writing CREATE TABLE manually
• You need to avoid writing and maintaining import scripts

Step 1: Sign in to Estuary

Register or log in at dashboard.estuary.dev.

Step 2: Create a New Capture

Navigate to Sources and click + New Capture. Select:

• HTTP File connector if your CSV is hosted at a URL (including S3 pre-signed URLs)
• Google Sheets connector if the source is a Google Sheet

Note on Google Sheets sync: The Google Sheets connector polls on a configurable interval and performs a full sheet refresh on each poll rather than incremental row-level sync. For large sheets that update frequently, factor in the additional read volume this creates.

Provide the CSV URL or Sheets link. Estuary fetches a sample of the file and infers column names and types automatically. Review the inferred schema before proceeding — you can override any type in the collection spec.

Note for HTTP File: Before connecting, verify the URL supports HEAD requests and returns aLast-Modifiedheader. Test with: curl -I https://your-csv-url.com/file.csv. IfLast-Modifiedis absent from the response, the connector will not be able to detect file updates.

Step 3: Save and Publish the Capture

Click Next, review the collection spec, then click Save and Publish. Estuary begins reading the CSV immediately.

Step 4: Create a PostgreSQL Materialization

Navigate to Destinations and click + New Materialization. Select the PostgreSQL connector and fill in the endpoint config:

• Address: your PostgreSQL host and port (e.g., localhost:5432)
• User: a PostgreSQL user with CREATE TABLE and INSERT privileges
• Password
• Database: target database name
• Database Schema: target schema (default: public)

Click Next. Estuary maps the captured collection to a PostgreSQL table, creating it automatically if it does not exist.

Step 5: Save and Publish

Click Save and Publish. Estuary performs the initial load, then monitors the CSV source for changes and syncs updates continuously.

What Estuary handles that manual COPY does not:

• Schema inference from CSV headers
• Automatic table creation in PostgreSQL
• Encoding normalization
• Ongoing sync when the source file changes
• Deduplication on re-imports

Method 2: Import CSV Using the PostgreSQL COPY Command

Best for: Large CSV files on the database server, one-time bulk loads, maximum import speed.

The COPY command is PostgreSQL's native bulk loader. It bypasses row-by-row INSERT overhead and loads directly into the table's heap files, making it significantly faster for large files.

Full COPY syntax reference

sql
COPY table_name (column1, column2, column3)
FROM '/absolute/path/to/file.csv'
WITH (
  FORMAT CSV,
  HEADER TRUE,
  DELIMITER ',',
  ENCODING 'UTF8',
  NULL '',
  QUOTE '"',
  ESCAPE '"'
);

Parameter reference:

Step-by-step: importing student_info CSV

Step 1: Prepare the CSV file. Confirm encoding with file -i students.csv. If it returns iso-8859-1, either convert it first with iconv or specify ENCODING 'LATIN1' in the COPY command.

bash
# Convert encoding if needed
iconv -f ISO-8859-1 -t UTF-8 students_latin.csv > students_utf8.csv

Step 2: Connect to PostgreSQL:

bash
psql -U postgres -d your_database

Step 3: Confirm the target table exists (see Prerequisites above). Then run the import:

sql
COPY student_info (first_name, last_name, dob, city)
FROM '/var/data/students.csv'
WITH (FORMAT CSV, HEADER TRUE, ENCODING 'UTF8', NULL '');

Step 4: Verify row count:

sql
SELECT COUNT(*) FROM student_info;

Using \copy for client-side files

If the CSV is on your local machine (not the server), use \\copy in psql:

sql
\\copy student_info (first_name, last_name, dob, city)
FROM '/Users/yourname/Downloads/students.csv'
WITH (FORMAT CSV, HEADER TRUE, ENCODING 'UTF8', NULL '');

\\copy uses identical syntax to COPY but reads from the client filesystem. No superuser privilege required.

Method 3: Import CSV Using pgAdmin

Best for: One-off imports where you prefer a GUI over command line, or when you do not have direct psql access.

Step 1: Connect to your database in pgAdmin

Open pgAdmin, expand the server tree, and navigate to your target database and schema.

Step 2: Open the Import dialog

Right-click the target table in the Object Explorer. Select Import/Export Data. In the dialog that opens, set the toggle to Import.

Step 3: Configure the import settings

• Filename: browse to your local CSV file
• Format: CSV
• Encoding: match the file encoding (check with file -i if unsure)
• Under Options: toggle Header on if your CSV has a header row
• Set Delimiter to , (or whatever separator your file uses)
• Set NULL Strings to match how nulls appear in your file (commonly empty string or NULL)

Step 4: Map columns (Columns tab)

Switch to the Columns tab. Verify the column mapping between CSV and table. If your CSV has columns in a different order than the table, reorder them here.

Step 5: Run the import

Click OK. pgAdmin runs \\copy behind the scenes and reports rows imported or any errors encountered.

CSV Import Validation Checklist

Run these checks in PostgreSQL immediately after any import method.

sql
-- 1. Row count matches source
SELECT COUNT(*) FROM student_info;

-- 2. Check for unexpected NULLs in required columns
SELECT COUNT(*) FROM student_info WHERE first_name IS NULL OR last_name IS NULL;

-- 3. Check date range sanity
SELECT MIN(dob), MAX(dob) FROM student_info;

-- 4. Check for duplicate primary keys (if importing IDs)
SELECT student_id, COUNT(*) FROM student_info
GROUP BY student_id HAVING COUNT(*) > 1;

-- 5. Spot-check 5 random rows
SELECT * FROM student_info ORDER BY RANDOM() LIMIT 5;

Troubleshooting Common CSV Import Errors

These are the actual errors you will encounter and their exact fixes.

Handling mixed encodings in a single file

This is the most common real-world scenario: a CSV exported from Excel on Windows that contains accented characters, em dashes, or currency symbols mixed with standard ASCII.

bash
# Detect encoding
file -i problematic.csv
# or
chardet problematic.csv  # requires Python chardet library

# Convert to UTF-8
iconv -f WINDOWS-1252 -t UTF-8 problematic.csv > clean.csv

# Verify conversion succeeded (should return no errors)
iconv -f UTF-8 -t UTF-8 clean.csv > /dev/null

Handling non-standard date formats

If your CSV contains dates in MM/DD/YYYY format rather than ISO YYYY-MM-DD:

sql
-- Option 1: Set datestyle for the session before running COPY
SET datestyle = 'ISO, MDY';
COPY student_info FROM '/path/students.csv' WITH (FORMAT CSV, HEADER TRUE);

-- Option 2: Import as TEXT into a staging table, then cast
CREATE TEMP TABLE staging_import (
  first_name TEXT, last_name TEXT, dob TEXT, city TEXT
);
COPY staging_import FROM '/path/students.csv' WITH (FORMAT CSV, HEADER TRUE);

INSERT INTO student_info (first_name, last_name, dob, city)
SELECT first_name, last_name, TO_DATE(dob, 'MM/DD/YYYY'), city
FROM staging_import;

Comparison: All Three Methods

Conclusion

For a one-time import of a well-formed CSV, the COPY command is the fastest and most reliable path. For files on your local machine, \\copy removes the superuser requirement with no other tradeoffs. For CSVs that update regularly or are hosted at a URL, Estuary eliminates the manual re-import cycle entirely.

The troubleshooting table above covers 95% of real-world CSV import failures. Encoding issues and date format mismatches are responsible for the majority of errors — validate both before running any import.

Start your free Estuary pipeline