Getting annoyed with Postgres Sequences

rubyonrails-talk
1

Hi,

I ahve a number of rails apps that use Postgres and every so often I need to delete all the tables and reimport the data directly into the database, doing this all works fine until a record tries to be added but because the sequences used for the id’s have been reset, I get errors due to the id already existing.

I assume MySQL works differently, but is there a way to avoid having to do multiple queries such as:

SELECT setval('authors_id_seq', (SELECT MAX(id)+1 FROM authors)); to update the sequences. IS there a way to make it work similarly to MySQL and just use the highest id that exists + 1 by default?

This really really bugs me and i’ve finally got around to asking here to see if anyone knows of a solution?

2

I have often gotten the sequence to reset by using TRUNCATE TABLE foo in MySQL. Never tried it in PostgreSQL.

Walter

3

It’s not that I want the sequences to reset, they do reset. Basically when i do an import of bulk data, I want the sequence to automatically be set to the highest id +1 of the new records without me having to do it manually for each table every time.

4

Not sure I understand the problem. Postgres adds the SELECT pg_catalog.setval('public.versions_id_seq', 62629, true);, etc calls by default when you do a database dump via pg_dump, so the sequences always start off at the last one that was used.