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 (column adbin in catalog pg_attrdef). That expression tree does not contain the name of the sequence, but its object ID, which is unchanged by renaming the sequence. Tools like psql's \d re-construct a string from the parsed expression, so the DEFAULT 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 catalog pg_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.