How can I safely rename a sequence in PostgreSQL, ideally without downtime?
If the sequence is used in the
DEFAULT
clause of the table column, it is enough to rename the sequence.That is because the
DEFAULT
clause is not stored as string, but as parsed expression tree (columnadbin
in catalogpg_attrdef
). That expression tree does not contain the name of the sequence, but its object ID, which is unchanged by renaming the sequence. Tools likepsql
's\d
re-construct a string from the parsed expression, so theDEFAULT
clause will appear to reflect the renaming.If the sequence name is used elsewhere, like in your client code or in a PostgreSQL function, you would have to change the name in that code. PostgreSQL functions are stored as strings (column
prosrc
in catalogpg_proc
), so renaming a sequence can make a function that uses the sequence fail.In this case, you would have to suspend activity until you have changed the code and renamed the sequence if you want to avoid errors.