How to import CSV into postgresql that already has an identifier?

Question

I have 3 CSV files, each of which contains about 150 thousand lines. They have already received the CSV ID, and associations are already held in them. Is there an easy way to skip the automatic assignment of id and instead use what is already in the CSV?

Answer 1

A serial column only draws the next number from the default sequence. If you write a value to it, the default value will not be entered. 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 is the sequential column of the tbl table, a picture from the tbl_tbl_id_seq sequence (default name).

Best in one transaction in case of simultaneous loading.

Please note: there is no “offside” error. In the documentation:

The two-parameter form sets the last_value field of the sequence to the specified value and sets its is_called field to true, which means that next nextval will advance the sequence before returning the value.

My bold accent.

Answer 2

You can directly copy CSV records to the POSTGRES table.

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

Following the method described above, we can actually avoid creating a record through an ActiveRecord object.