One of my clients has a database in which a single identity column (called id in that table) has two sequences associated with it(!)
Both sequences display
Sequence for identity column: <schema>.<table>.id when described with \d in psql.
Inserting into the table fails with ERROR: more than one owned sequence found, as does trying to alter the table to drop the identity on that column.
Trying to drop either sequence results in
ERROR: cannot drop sequence <name> because column id of table <name> requires it HINT: You can drop column id of table <name> instead. while trying to alter either sequence owned by none results in
ERROR: cannot change ownership of identity sequence DETAIL: Sequence "<name>" is linked to table "<name>". How do we fix this? I presume we need to update the catalog directly to dissociate one of the sequences and after that drop the orphaned sequence.
This is in a Postgres 12.22 database that we're trying to upgrade to Postgres 17, but we get ERROR: column "id" of relation "<name>" is already an identity column when trying to upgrade.
