PostgreSQL COPY Error: Fix Invalid Input Syntax in CSV Import

Quick Answer

PostgreSQL's COPY command rejects CSV files for six predictable reasons: data type mismatches between CSV values and table column types, wrong column count per row, a BOM character at the start of the file, NULL values in NOT NULL columns, encoding mismatch, and the critical COPY vs \copy path distinction.

The fix: Pre-validate your CSV against your table schema before running COPY. Clean type mismatches, strip BOM characters, verify column counts, and handle NULL representations explicitly.

Why it happens: PostgreSQL's COPY is strict by design. Unlike spreadsheet importers, it does not attempt to cast or convert values — if a CSV cell contains "na" and the column is NUMERIC, COPY throws an error on that exact row. There is no partial import.

---

What PostgreSQL's Error Messages Actually Mean

ERROR: invalid input syntax for type integer: "value" (SQL state: 22P02) — A CSV cell contains text where the table column expects an integer. Check for header row included in data, currency symbols ($), commas in numbers, or text null representations like "na" or "NULL".

ERROR: invalid input syntax for type numeric: "$1.00" (SQL state: 22P02) — Currency-formatted values with $, €, or commas cannot be parsed as NUMERIC. Strip formatting before import.

ERROR: invalid input syntax for type date: "12/09/2011" (SQL state: 22007) — Date format mismatch. PostgreSQL expects YYYY-MM-DD by default. US-format dates (MM/DD/YYYY) require explicit DATEFORMAT specification or pre-conversion.

ERROR: missing data for column "colname" (CONTEXT: COPY table, line N) — The row at line N has fewer comma-separated values than the table has columns. Check for truncated rows, mismatched delimiters, or unmatched quotes causing multiple rows to merge.

ERROR: extra data after last expected column — The row has more values than the table has columns. Check for embedded commas in unquoted fields.

ERROR: invalid input syntax for integer: "3192" (BOM character) — The invisible  (U+FEFF) character at the start of the file is being read as part of the first field's value. The file has a UTF-8 BOM that must be stripped.

ERROR: value too long for type character varying(N) — A text value exceeds the column's defined character limit. Run SELECT MAX(LENGTH(column_name)) FROM table_name to identify the longest values.

ERROR: null value in column "colname" of relation "table" violates not-null constraint — Empty CSV cells are being interpreted as NULL, but the column has a NOT NULL constraint. You must handle empty values explicitly.

---

Fast Fix (2 Minutes)

If your PostgreSQL COPY command just failed, try this first:

1. Read the CONTEXT line — PostgreSQL tells you the exact line number. Count to that line in your CSV and inspect the value.
2. Check for BOM — If the error is on line 1 and shows a garbled character before your first value, open the file in Notepad++ → Encoding → check if it shows "UTF-8-BOM". Remove it.
3. Check column count — Run SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'yourtable' and verify it matches your CSV column count.
4. Test with 5 rows — Create a 5-row test CSV with your cleanest data. If that imports, the problem is in a specific row of your full file.
5. Add NULL AS option — If you have empty cells: COPY table FROM 'file.csv' WITH (FORMAT CSV, HEADER, NULL '') treats empty strings as NULL.

If the error persists on specific rows, continue below.

---

TL;DR: PostgreSQL COPY is all-or-nothing — one bad row fails the entire import. The most common causes are data type mismatches (text in numeric columns, wrong date formats), BOM characters making line 1 fail, and column count errors from unquoted fields with embedded commas. Validate your CSV with SplitForge Data Validator to find all errors before running COPY.

---

You're migrating 2 million rows of transaction data into PostgreSQL. You run the COPY command. Six seconds later: ERROR: invalid input syntax for type numeric: "na" CONTEXT: COPY transactions, line 847, column amount: "na". The import rolls back entirely. You're at zero rows imported.

PostgreSQL COPY is transactional. One bad row cancels every row. This is by design — partial imports create data integrity problems worse than failed ones. The solution is fixing the CSV before running COPY, not finding workarounds after.

Each error was reproduced using PostgreSQL 16 COPY command via psql, March 2026.

---

---

COPY Format Options Quick Reference

Full example with common options:

COPY my_table FROM '/path/file.csv'
WITH (FORMAT CSV, HEADER, NULL '', ENCODING 'UTF8', DELIMITER ',');

---

How PostgreSQL COPY Compares to Other Import Systems

PostgreSQL COPY is the strictest of all: one bad row in 2 million rows fails everything. The tradeoff is complete data integrity — there is no partial import.

---

Table of Contents

• COPY vs \copy: The Path Problem
• Fix 1: Data Type Mismatches
• Fix 2: Date Format Errors
• Fix 3: Column Count Errors
• Fix 4: BOM Character on Line 1
• Fix 5: NULL and Empty Value Handling
• Fix 6: Encoding Mismatch
• Pre-Import Validation Workflow
• Additional Resources
• FAQ

---

This guide is for: Database administrators, backend developers, and data engineers importing CSV data into PostgreSQL using the COPY command or pgAdmin's import feature. Basic SQL knowledge is assumed.

---

COPY vs \copy: The Path Problem

Before troubleshooting any error, confirm which command you're using. This distinction is the most commonly missed source of "file not found" and permission errors.

COPY (uppercase, no backslash): Runs server-side. The file path is resolved by the PostgreSQL server process, not your local machine. The path must be accessible by the postgres OS user on the server. Use this when running SQL directly in the server environment.

\copy (lowercase, with backslash): Runs client-side via psql. The file path is resolved by your local machine. Use this when running psql from your workstation and the CSV is on your local disk.

-- Server-side (file must be on the database server)
COPY my_table FROM '/var/data/import.csv' WITH (FORMAT CSV, HEADER);

-- Client-side (file is on your local machine)
\copy my_table FROM '/Users/yourname/import.csv' WITH (FORMAT CSV, HEADER);

If you're getting ERROR: could not open file for reading: No such file or directory — switch to \copy.

Fix 1: Data Type Mismatches

invalid input syntax for type errors mean a CSV value cannot be converted to the target column's data type. PostgreSQL does not attempt type coercion during COPY — it either matches exactly or fails.

Common causes and fixes:

Currency symbols in numeric columns — $1,250.00 cannot be parsed as NUMERIC. Strip all $, €, £, and thousands-separator commas from numeric fields before import.

❌ BAD row:   101,John Doe,$1,250.00,2026-03-15
✅ FIXED row: 101,John Doe,1250.00,2026-03-15

Null-like strings in numeric columns — Values like "na", "N/A", "null", "-", or empty strings in numeric columns cause this error. Handle them explicitly:

❌ BAD row:   101,John Doe,na,2026-03-15
✅ FIXED row: 101,John Doe,,2026-03-15   ← empty = NULL (with NULL '' option)

Then in your COPY command:

COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, NULL 'na');

Header row included in data — If your COPY command doesn't include HEADER but your CSV has a header row, PostgreSQL tries to parse the header as data values. Always include HEADER when your CSV has column headers:

❌ ERROR: COPY my_table FROM 'file.csv' WITH (FORMAT CSV);
          → "invalid input syntax for integer: "id""
✅ FIXED:  COPY my_table FROM 'file.csv' WITH (FORMAT CSV, HEADER);

Use SplitForge Data Validator to identify all type mismatch cells across your entire CSV before running COPY. It validates each column's values against a specified data type and flags non-conforming cells by row number.

Fix 2: Date Format Errors

PostgreSQL's default date format is YYYY-MM-DD (ISO 8601). US-format dates (MM/DD/YYYY) and European dates (DD/MM/YYYY) are not interpreted correctly without explicit configuration.

Error: ERROR: invalid input syntax for type date: "12/09/2011"

The exact problem:

❌ BAD:   id,name,event_date
          1,Acme Corp,12/09/2011     ← MM/DD/YYYY fails by default
✅ FIXED: id,name,event_date
          1,Acme Corp,2011-12-09     ← YYYY-MM-DD works always

Fix Option A — Pre-convert dates to ISO format:

Convert all dates to YYYY-MM-DD before running COPY. This is the most reliable approach and works regardless of PostgreSQL locale settings.

Fix Option B — Import as text, then cast:

Import the date column as TEXT, then cast in a post-import UPDATE:

-- Import into staging table with date as TEXT
COPY staging_table (id, name, date_text) 
FROM '/path/file.csv' WITH (FORMAT CSV, HEADER);

-- Cast and insert into final table
INSERT INTO final_table (id, name, event_date)
SELECT id, name, TO_DATE(date_text, 'MM/DD/YYYY')
FROM staging_table;

Fix Option C — Set datestyle for the session:

SET datestyle = 'ISO, MDY'; -- For MM/DD/YYYY inputs
\copy my_table FROM 'file.csv' WITH (FORMAT CSV, HEADER);

For mixed date formats across different columns or rows, see our guide on fixing mixed date formats in CSV files.

Fix 3: Column Count Errors

missing data for column and extra data after last expected column both indicate row-level column count mismatches between the CSV and the table schema.

Most common cause — unquoted fields with embedded commas:

A CSV cell containing a comma must be wrapped in double quotes. If it isn't, PostgreSQL splits on the embedded comma and creates extra columns.

❌ BAD:   John Doe,New York, NY,2026-03-15
          ↑ PostgreSQL sees 4 columns instead of 3

✅ FIXED: John Doe,"New York, NY",2026-03-15
          ↑ PostgreSQL sees 3 columns correctly

Second cause — unmatched double quotes causing row merges:

An unclosed quote in one row causes PostgreSQL to continue reading subsequent rows as part of the same field. The result is rows that appear to have too few columns.

Fix — find the exact row:

PostgreSQL's error includes the line number. Count to that line in your CSV and inspect manually. For large files, validate structure with SplitForge Data Validator to find all column count errors across the full file at once.

For a complete guide to column count mismatches, see CSV column count mismatch fix.

Fix 4: BOM Character on Line 1

A UTF-8 BOM (Byte Order Mark, U+FEFF) is an invisible character that some Windows text editors and Excel add to the beginning of UTF-8 files. PostgreSQL reads it as part of the first field's value, causing the first field to contain  + the actual value.

Symptom: ERROR: invalid input syntax for integer: "3192" — The garbled character before 3192 is the BOM.

Fix Option A — Strip BOM in PostgreSQL 16+:

COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, ENCODING 'UTF8BOM');

Fix Option B — Strip BOM before import:

Open the file in Notepad++ → Encoding → Convert to UTF-8 (not "UTF-8 with BOM"). Save and re-import.

Fix Option C — Use sed (Linux/Mac):

sed -i '1s/^\xEF\xBB\xBF//' yourfile.csv

This strips the three BOM bytes from the first line.

For more on BOM issues in CSV files, see our BOM CSV fix guide.

Fix 5: NULL and Empty Value Handling

PostgreSQL's COPY interprets empty CSV fields as empty strings by default, not as NULL. If a column has a NOT NULL constraint or is typed as NUMERIC, an empty string fails.

Explicitly specify NULL representation:

-- Treat empty strings as NULL
COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, NULL '');

-- Treat 'na' as NULL (for data from R, Python pandas exports)
COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, NULL 'na');

-- Treat 'NULL' string as NULL
COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, NULL 'NULL');

For NOT NULL columns, replace empty cells with a default value before importing. Use find-replace in a text editor or SplitForge Data Cleaner to bulk-replace empty cells across large files.

Fix 6: Encoding Mismatch

PostgreSQL defaults to the database's encoding, typically UTF-8. Files exported from Windows systems are often Windows-1252 or ANSI-encoded. Character mismatch produces invalid byte sequence errors.

Specify encoding explicitly:

COPY my_table FROM '/path/file.csv' 
WITH (FORMAT CSV, HEADER, ENCODING 'WIN1252');

Supported encoding names in PostgreSQL are listed in the official documentation at PostgreSQL: Supported Character Sets.

Pre-Import Validation Workflow

Running COPY directly on a large CSV and debugging errors line by line is slow. Use this workflow to validate before committing:

1. Run COPY on the first 100 rows — Create a test CSV with just the header + first 100 rows and run COPY on it. This surfaces type and structure errors quickly.
2. Check column count — awk -F',' '{print NF}' file.csv | sort | uniq -c shows how many rows have each column count. All rows should show the same number.
3. Check for BOM — hexdump -C file.csv | head -1 — If the first bytes are ef bb bf, there's a BOM.
4. Validate types before import — Use SplitForge Data Validator to check every column's values against expected types. For a 2-million-row file, this catches every error before COPY runs rather than after.

For related MySQL import errors using a similar LOAD DATA INFILE pattern, see MySQL CSV import 1064 syntax error fix.

Additional Resources

PostgreSQL Official Documentation:

• PostgreSQL COPY Command Reference — Official COPY syntax, options, NULL handling, and encoding parameters
• PostgreSQL Supported Character Sets — Full list of encoding names for the ENCODING option
• PostgreSQL Data Types — Type definitions and input format requirements

Standards:

• RFC 4180: CSV Format Specification — Official quoting and delimiter rules PostgreSQL COPY follows
• Unicode UTF-8 Standard — UTF-8 and BOM specification

FAQ