Last Updated: February 25, 2016
·
326
· mohangk

Cannot drop table due to dependent sequence despite removing it as the default of the column?

Sometimes you need to recreate a database table to work around the fact that you can't alter the table state to what you require. For example, making a table into an unlogged table or vice versa. The easiest way to do that would be to run a query as follows:

CREATE UNLOGGED TABLE unlogged_table AS select col1, col2 FROM table;

Once this is done you can move the sequence from the first table to the second as follows,

ALTER TABLE unlogged_table ALTER COLUMN "col1" SET DEFAULT nextval('"public"."table_seq");
ALTER TABLE table ALTER COLUMN "col1" SET DEFAULT NULL;

However if you now try to drop table now, you will get an error that the sequence is still dependent on table. Turns out you also need to change the OWNER of the table as follows:

ALTER SEQUENCE table_seq OWNED BY unlogged_table.col1;