Bigint error when copying .csv to postgresql

Question

Trying to import a .csv into my postgres table using the following approach:

System: WSL2 - UBUNTU 20.04

psql -d db_name --user=username -c "\copy test_list FROM 'testmngrs.csv' delimiter '|' csv;"

The content format of my .csv:

1,Name,name@store_id.com,1234567891,City Name

The error I'm receiving:

ERROR:  invalid input syntax for type bigint:
CONTEXT:  COPY test_list, line 1, column id:

The table:

SELECT * FROM test_list;
id | store_id | name | email | phone | city

The additional id at the head of the table above was not something created during my initial set up of the table.

My ecto migration file is as follows:

I'm not sure what's causing the BigInt error, nor how to avoid it as I copy over the data. I'm also a bit confused as to why there's an additional id column in my table given that it was never defined in my migration

I'm pretty new to postgresql and elixir / ecto so any assistance is greatly/guidance/context is greatly appreciated!

Adam Millerchip · Accepted Answer · 2022-11-26 11:14:21Z

1

From the docs:

By default, the table will also include an :id primary key field that has a type of :bigserial.

Ecto assumes you want it to generate the id field by default. It's better to just go with it. But you can configure it somewhat counter-intuitively by setting primary_key: false on the table, and primary_key: true on the column:

create table(:managers, primary_key: false) do
  add :store_id, :integer, null: false, primary_key: true
  ...

answered Nov 26, 2022 at 11:14

Adam Millerchip

23.3k6 gold badges62 silver badges86 bronze badges

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

1 Comment

Chris Rad Over a year ago

Thank you for that additional context! I ended up dropping the id column and concatenating my .csv into a .sql file to insert into the db.

Collectives™ on Stack Overflow

Bigint error when copying .csv to postgresql

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