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 http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php

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.