删除 PostgreSQL 中的表而不删除关联序列
我有一张桌子，foo。为了快速升级/部署我的网站，我创建了一个新表 tmp_foo 来包含一些新数据，方法是：
create table tmp_foo (like foo including constraints including defaults including indexes);
现在每个表都有一个 PK id > 列如下所示：
Column | Type | Modifiers
-------------+-----------------------+--------------------------------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
重要的一点是，两个表都依赖于完全相同的序列 foo_id_seq。没有tmp_foo_id_seq。这对于我的目的来说似乎没问题。
之后，我用新数据加载了 tmp_foo 并重命名了表，以便 tmp_foo 接管了真正的 foo 和原始 foo 变成了 foo_old。现在我尝试删除 foo_old：
db=> drop table foo_old ;
ERROR: cannot drop table foo_old because other objects depend on it
DETAIL: default for table foo_old column id depends on sequence foo_id_seq
公平地说，id 列默认值仍然取决于序列。
db=> alter table foo_old alter column id drop default;
这是最关键的。
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
因此 foo_old 不再对序列有任何可见的依赖关系，但它仍然尝试将序列与表一起删除（显然不会，因为新表依赖于它）。
所以问题分为两部分：
- 为什么序列仍然链接 与旧桌子？
- 有没有 解决这个问题的方法不涉及 使新表依赖于 新的或不同的序列（如果 甚至会有所帮助）？
（在 PostgreSQL 8.4 上）
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 table 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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
试试这个：
然后你应该能够删除桌子。
要检索序列的“所有者”，请使用以下查询
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)Try this:
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)