PostgreSQL: Be careful when creating records with specific ids

In tests, it is sometimes useful to create records with specific ids. On PostgreSQL this can cause problems:

Usually, PostgreSQL uses an "autoincrement" sequences to provide sequential ids for new database rows. However, these sequences will not increment if you insert a record "by hand". This will cause an error:

Copyrecord = Record.create!
record.id                             # => 100, next automatic id will be 101
Record.create!(id: record.id + 1)     # okay, but next automatic id will still be 101
Record.create!                        # will cause an SQL error 'duplicate key value violates unique constraint "records_pkey"'

Workaround

In tests, one workaround would be to simply initialize all sequences with a large value, so that you can safely insert records will small ids without running into conflicts:

How to fix messed up sequences

When you need to insert data with ids outside of the tests you need to fix the sequence afterwards, or you will be unable to create new records for the table you inserted the data into.
You can use reset_pk_sequence! Show archive.org snapshot for this.
This will set your sequence to the highest id found in the table.

E.g. in a migration do:

Copyreset_pk_sequence!(:your_table_name)