PSQL: Invalid input syntax for integer on COPY

Question

I have a .txt file with a plain list of words (one word on each line) that I want to copy into a table. The table was created in Rails with one row: t.string "word".

The same file loaded into another database/table worked fine, but in this case I get:

pg_fix_development=# COPY dictionaries FROM '/Users/user/Documents/en.txt' USING DELIMITERS ' ' WITH NULL as '\null';
ERROR:  invalid input syntax for integer: "aa"
CONTEXT:  COPY dictionaries, line 1, column id: "aa"

I did some googling on this but can't figure out how to fix it. I'm not well versed in SQL. Thanks for your help!

mu is too short · Accepted Answer · 2015-02-23 22:32:59Z

1

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 ....

answered Feb 23, 2015 at 22:32

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

bcflow Over a year ago

Thanks! That worked. I deleted the created_at and updated_at columns and used your syntax.

Collectives™ on Stack Overflow

PSQL: Invalid input syntax for integer on COPY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related