在PostgreSQL中删除表而不删除相关序列 [英] Deleting a table in PostgreSQL without deleting an associated sequence

问题描述

我有一张桌子， foo 。为了快速升级/部署我的网站，我做了一个新表 tmp_foo ，它包含以下内容：

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);

现在每个表都有一个PK id 列看起来像这样：

Now each table has a PK id column that looks like:

   Column    |         Type          |                                Modifiers                                 
-------------+-----------------------+--------------------------------------------------------------------------
 id          | integer               | not null default nextval('foo_id_seq'::regclass)

重要的是两个表都依赖完全相同的序列 foo_id_seq 。没有 tmp_foo_id_seq 。

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.

此后，我用新数据加载了 tmp_foo 并重命名了表，以便 tmp_foo 取代了真正的 foo ，而原来的 foo 变为 foo_old 。现在，我尝试删除 foo_old ：

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

足够好， id 列的默认值仍然取决于顺序。

Fair enough, the id column default still depends on the sequence.

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 不再对序列有任何可见的依赖关系，但仍尝试将序列与表一起删除（并且显然不会，因为新表依赖它。）

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).

所以问题分为两部分：

1. 为什么序列仍将
   与旧表链接？


2. 是否有任何不涉及
   的
   方式，使新表依赖于
   的新序列或其他序列（如果
   甚至有帮助）？

（在PostgreSQL 8.4上）

(On PostgreSQL 8.4)

推荐答案

尝试一下：

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)

这篇关于在PostgreSQL中删除表而不删除相关序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！