By default, the error message tells you more. Like:
ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE to drop the dependent objects too.
Include the object mentioned in the
Detail line in your script.
Also, to do it safely, wrap all of it in a transaction, and write-lock the old table before working on the new to avoid losing work done during the transition:
LOCK TABLE tbl IN SHARE MODE;
-- prepare tbl_cleaned
DROP VIEW v_tbl; -- or any other depending object
DROP TABLE tbl;
ALTER TABLE tbl_cleaned rename to tbl;
CREATE VIEW v_tbl AS SELECT ... -- actual definition, see below
There may be more depending objects. Same procedure.
In the example, to get the view definition:
SELECT 'CREATE VIEW v_foo1 AS '
|| pg_get_viewdef('v_foo1'::regclass) AS view_definition;
And you may want to rename constraints and indices to fit the renamed table - or hold back on those and create with correct names after renaming the new table. (Again, be sure to get the definition before dropping the old table!)
If there are many depending objects and the table is not too big, a good alternative might be to prepare the new table (possibly as temporary table for better performance), then
TRUNCATE the original and
INSERT from the prepared new table. This way, depending objects like views, FK constraints, rules, function using the table type etc. can just stay in place. See:
Or you might be able to update the old table in place? This related answer shows code for all three variants:
Your particular error message:
DETAIL: default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq
... indicates that you are (ab-)using a sequence to generate default values for multiple tables. Or at least it's owned by the wrong column. The solution depends on how you want to deal with the situation.
Or you created the new table copying the complete structure including default values depending on the same sequence.
Typically, you want a dedicated sequence. So you should probably make the new column own the
ALTER SEQUENCE cals_status_id_seq OWNED BY tbl_cleaned.id; -- column in new table *before* switching
DROP TABLE only cascades to the sequence if it's owned by one of the columns in the table to be dropped.
You may be interested in
IDENTITY columns which avoid this kind of complication with
serial columns. See: