3

I have 3 CSV files that are about 150k rows each. They already have been given ID's in the CSV and the assosciations are held within them already. Is there a simple way to skip the auto-assignment of the id value and instead use what is already in the CSV?

yburyug
  • 1,070
  • 1
  • 8
  • 13

2 Answers2

5

A serial column only draws the next number from a sequence by default. If you write a value to it, the default will not kick in. You can just COPY to the table (see @Saravanan' answer) and then update the sequence accordingly. One way to do this:

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id being the serial column of table tbl, drawing from the sequence tbl_tbl_id_seq (default name).

Best in a single transaction in case of concurrent load.

Note, there is no off-by-1 error here. Per documentation:

The two-parameter form sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value.

Bold emphasis mine.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks! This worked great. Except, note that in my case, sequence id columns in my tables are simply named `id`, so the command I ended up using successfully was `SELECT setval('tbl_id_seq', max(id)) FROM tbl;`. – jaredscheib Mar 08 '19 at 01:55
2

You can directly copy the CSV records to POSTGRES table.

COPY table_name FROM '/path/to/csv' DELIMITER ',' CSV;

By following the above method, we can actually avoid create a record through ActiveRecord object.

Saravanan
  • 515
  • 3
  • 14
  • What about the sequence that supplies values for the `id` column? – mu is too short Apr 01 '14 at 18:53
  • @mu That has to be reset to the value accordingly isn't? – Saravanan Apr 01 '14 at 18:57
  • 1
    I'm pretty sure COPY won't do it for you so that COPY will leave you with a busted table (i.e. future INSERTs are likely to fail due to duplicate PKs) unless you do a [`setval`](http://www.postgresql.org/docs/current/static/functions-sequence.html) to fix the sequence. – mu is too short Apr 01 '14 at 19:05
  • PS: You can do this through the underlying PostgreSQL driver that Rails will be using: [`copy_data`](http://rubydoc.info/gems/pg/PG/Connection:copy_data) – mu is too short Apr 01 '14 at 19:38