Postgres COPY invalid syntax for integer

Question

My table,

       Table "public.product_status"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 code   | character varying(10) |
 notes  | text                  |

First line of my CSV file,

1,S,blah blah blah

Copy command,

=# copy product_status from 'status_table.csv' with csv;
ERROR:  invalid input syntax for integer: "1"
CONTEXT:  COPY product_status, line 1, column id: "1"

Really don't understand how that's invalid syntax for an integer! Even postgres seems to agree with me,

=# insert into product_status values (1,'S','blah blah blah');
=# copy product_status to STDOUT with csv;
1,S,blah blah blah

Any ideas? Other questions I found on the site didn't seem applicable to whatever I've done wrong here.

Laurenz Albe · Accepted Answer · 2017-03-22 10:01:20Z

3

There is probably an “invisible” character at the beginning of the file.

Examine the contents; if you are on sume UNIX, you could use od -c status_table.csv.

Chances are that there is a useless byte order mark at the beginning of the file. Just remove it.

answered Mar 22, 2017 at 10:01

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dune.rocks Over a year ago

Thank you Laurenz, I did have BOM in my file!

dbwells · Accepted Answer · 2017-07-05 21:26:15Z

1

I had this same issue, and my answer was the same as above, a "useless byte order mark at the beginning of the file." Here is how I removed it.

1) Open the file in vim

vim file_with_bom.txt

2) Set the 'nobomb' option

:set nobomb

3) Save the file and exit

:wq

Hope this helps!

answered Jul 5, 2017 at 21:26

dbwells

213 bronze badges

Collectives™ on Stack Overflow

Postgres COPY invalid syntax for integer

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related