How to insert data with specific ID into Postgres using Sequel

Question

I'm populating a Postgres database using the Sequel gem. However, the data is pulled from another DB and already contains an ID. I'm only pulling a very small subsection of those ID's, and they are completely random, so they are very much out of order.

Is it possible to insert items into my database and provide the specific ID to use as a primary key, or is that a bad idea? I tried to provide the ID manually and got "id is a restricted primary key".

Answer 1

You can't insert the id, because the database and Sequel want to define the primary index value as a sequential number.

You can remove the :id from the hash using Hash's delete method:

foo = {id: 1, bar: 2}
foo.delete(:id) # => 1
foo # => {:bar=>2}

then use insert.

If you absolutely must reuse those IDs, then you could sort your data by the primary IDs, then loop over the records, inserting dummy records for the gaps and then the real data, allowing the DBM to use the next sequential number still. But that's really going against the flow of how we use a DB.