PostgreSQL – How to Delete One of Two Tables Using the Same Sequence

postgresql

I've just finished partitioning an existing table by 1) renaming the original table 2) creating a new partitioned tables with identical structure as the original, and 3) transferring the data from the old un-partitioned table to the new one.

The partitioning has worked just fine, no problems. The only issue is that I now have two tables with and identical field that both use the same sequence

ident integer NOT NULL DEFAULT nextval('mytable_ident_seq'::regclass)

Question – how do I remove the original table?

I can't delete the original table because in doing so Postgres attempts to drop the sequence, which causes an error because 'other objects depend upon it', and the 'other object' is my newly created, partitioned version.

The first thing I tried was to remove the reference to the sequence 

 ALTER TABLE mytable_old ALTER COLUMN ident SET DEFAULT 0;

This seems to work, it returns no errors, but when I look at the properties for the field it still says it has a dependency on the sequence, and when I attempt to drop the table I get the same error. The table currently has zero rows of data, and I've dropped all the fields except for this one, but I still can't actually drop the table itself.

This isn't a critical error, the database works ok, the new version of the table is partitioned and working fine, I'd just like to remove the old table for the sake of tidiness.

Best Answer

You were probably using a serial data type.

PostgreSQL documentation says this is equivalent to the following:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Note the latest statement.

Based on https://www.postgresql.org/docs/current/sql-altersequence.html you will need to remove the "ownership" dependency:

Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

Which in your case means:

ALTER SEQUENCE mytable_ident_seq OWNED BY NONE;

Or you may need to reassociate it with the proper table, using:

ALTER SEQUENCE mytable_ident_seq OWNED BY table_name.column_name;

After which you should be able to delete the table without consequences for the sequence.

Related Question