CODEWITHSUNDEEP
sqlruby-on-railspostgresql
yburyug
yburyug

Reputation: 1070

How to import a CSV to postgresql that already has ID's assigned?

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?

Upvotes: 3

Views: 2688

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

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.

Upvotes: 5

Saravanan
Saravanan

Reputation: 509

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.

Upvotes: 2

Related Questions