Pg Copy Error: Invalid Input Syntax for Integer

PG COPY error: invalid input syntax for integer

ERROR: invalid input syntax for integer: ""

"" isn't a valid integer. PostgreSQL accepts unquoted blank fields as null by default in CSV, but "" would be like writing:

SELECT ''::integer;

and fail for the same reason.

If you want to deal with CSV that has things like quoted empty strings for null integers, you'll need to feed it to PostgreSQL via a pre-processor that can neaten it up a bit. PostgreSQL's CSV input doesn't understand all the weird and wonderful possible abuses of CSV.

Options include:

• Loading it in a spreadsheet and exporting sane CSV;
• Using the Python csv module, Perl Text::CSV, etc to pre-process it;
• Using Perl/Python/whatever to load the CSV and insert it directly into the DB
• Using an ETL tool like CloverETL, Talend Studio, or Pentaho Kettle

PG COPY error: 'invalid input syntax for integer' when importing quoted CSV file without any integers

Try specifying the columns . . . without the primary key:

COPY customer_ (first_name_ text, last_name_ text, phone_ text, email_ text)
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

Without the column list, it is looking for a value for id_.

The import data file's first row of column names are not used for mapping to the table columns. The HEADER flag merely tells Postgres to skip over that first line, as documented:

HEADER

Specifies that… on input, the first line is ignored. …

Why am I getting "Invalid input syntax for type integer" in postgresql when importing a CSV?

Maybe there is a BOM in the CSV?

• hexdump the file, and inspect the first three characters
• (and) use an editor to remove the BOM
• (or) export again, without the BOM (there should be a checkmark, even in the Microsoft "software")

POSTgreSQL 9.5: invalid input syntax for integer when trying to copy from CSV

Sounds like your file 'tampadocs.csv' has a header line. Can you check on that? The copy syntax you're using is assuming there's only data in the file.

If there's a header line in your file, you can try the following:

COPY tampadocs
  FROM 'C:\Users\bam\Desktop\tampadocs.csv'
  WITH (FORMAT CSV, DELIMITER ',', HEADER);

That lets the copy statement know to expect a header line in the file. The full syntax for COPY is available here.

PSQL: Invalid input syntax for integer on COPY

If you created that table in Rails then you almost certainly have two columns, not one. Rails will add an id serial column behind your back unless you tell it not to; this also explains your "input syntax for integer" error: COPY is trying to use the 'aa' string from your text file as a value for the id column.

You can tell COPY which column you're importing so that the default id values will be used:

copy dictionaries(word) from ....

---

Related Topics