PostgreSQL Rename Sequence Bug/Feature Fix

In PostgreSQL ( < 8.3 I believe ), if you rename a sequence you don't fully rename it This is recorded in bug 3619

While the sequence will be renamed as an object, the metadata won’t…

PG stores the sequence name both as the object , and as ‘sequence_name’ in the sequence itself ( viewable via ‘SELECT * FROM sequence’ ; i think its in the postgres db catalog too )

Most people won’t run into an issue… until you use an ORM which is likely to pull the old sequence name from PG.

after much searching and trying, i found a fix. note: this only works for SMALL databases:

$ pg_dump -U$owner $db > $db.sql
$ psql -Upostgres
psql> DROP DATABASE $db;
psql> CREATE DATABASE $db WITH OWNER = $owner;
psql> q
$ psql -U$owner $db < $db.sql yes, that's the amazing fix -- backup up, dropping, and restoring the database. i'm sure there's a better fix out there somewhere -- i just couldn't find it with 2 hours of searching and syntax attempts. If you can find a better way, LET ME KNOW and I'll update this reference.

Leave a Reply

Your email address will not be published. Required fields are marked *