Deleting a table in PostgreSQL without deleting an associated sequence
I have a table, foo
. For the purposes of a quick upgrade/deploy of my site, I made a new table, tmp_foo
, to contain some new data, by doing:
create table tmp_foo (like foo including constraints including defaults including indexes);
Now each table has a PK id
column that looks like:
Column | Type | Modifiers
-------------+-----------------------+--------------------------------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
The important point is that both tables rely on the exact same sequence, foo_id_seq
. There is no tmp_foo_id_seq
. This seems OK for my purposes.
After this, I loaded tmp_foo
with new data and renamed the tables so that tmp_foo
took over as the real foo
, and the original foo
became foo_old
. Now I try to drop foo_old
:
db=> drop table foo_old ;
ERROR: cannot drop table foo_old because other objects depend on it
DETAIL: default for tab开发者_运维技巧le foo_old column id depends on sequence foo_id_seq
Fair enough, the id
column default still depends on the sequence.
db=> alter table foo_old alter column id drop default;
Here's the kicker.
db=> drop table foo_old ;
ERROR: cannot drop table foo_old because other objects depend on it
DETAIL: default for table foo column id depends on sequence foo_id_seq
So foo_old
no longer has any visible dependency on the sequence, yet it still tries to drop the sequence along with the table (and obviously won't because the new table depends upon it).
So the question is two-part:
- Why is the sequence still linked with the old table?
- Is there any way around this that doesn't involve making the new table depend on a new or different sequence (if that would even help)?
(On PostgreSQL 8.4)
Try this:
ALTER SEQUENCE foo_id_seq OWNED BY NONE
then you should be able to drop the table.
To retrieve the "owner" of a sequence use the following query
SELECT s.relname as sequence_name, n.nspname as sequence_schema, t.relname as related_table, a.attname as related_column FROM pg_class s, pg_depend d, pg_class t, pg_attribute a, pg_namespace n WHERE s.relkind = 'S' AND n.oid = s.relnamespace AND d.objid = s.oid AND d.refobjid = t.oid AND (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
精彩评论