Postgresql - invalid input syntax for csv import

Question

I am trying to import data from a .csv file to my my postgresql database using pgAdmin. One of the fields in my .csv file is price and I set its data type as Number. In my postgresql shcema, I set the data type for price as numeric. However, during the upload I keep getting ERROR: invalid input syntax for type numeric: "price".

I've tried setting the data type in my .csv file for price as general, accounting, currency, etc. but keep getting the same error. Does anyone have any idea how to fix this?

Note - in the import window of pgAdmin, I set the format to csv.

Thanks in advance!

Answer 1

Looks like it's failing on the header row? "price" seems to be a header, not a value. Try setting the "HEADER" option on csv import command or remove the header line from your csv file.

Answer 2

the only way to fix it is to start with valid data.

The most likely cause is that that spreadsheet has an entry that's not a number. (possibly a word instead)

try loading the CSV data into a table with a text column and using regex to find the rogue record(s)

Answer 3

The PostgreSQL database takes the first row of the CSV as the data row. Try to remove the Field names from the CSV and then try importing.

It solved my error.