如何在PostgreSQL中使一张表与另一张表共享序列？

Question

基本上我有一个表1，其中包含城市特征的唯一ID，现在我获得了一个表2，其中包含该国的特征。我需要为国家表创建新的ID（这些需要与城市共享相同的顺序，以便在交叉引用表时匹配ID）。如何使表2具有相同的ID，以匹配表1中的城市ID，并为其他地方的特征创建新的ID？实质上是共享序列。编辑：表已经创建好了，如何更新表2。

- Luffydude

Answer 1

如果您手动创建一个序列并将其作为ID列的默认值分配，那么它可以正常工作。但是，要重用现有值，这意味着我们必须创建一个触发器来分配现有值或从共享序列中获取新值。

create sequence baz;

create table foo(id bigint default nextval('baz'), value text);

create table bar(id bigint default nextval('baz'), value date);

insert into foo (value) values ('Hello');
insert into bar (value) values (now());
insert into foo (value) values ('World');
insert into bar (value) values (now());

select 'foo', id, value::text from foo 
union all
select 'bar', id, value::text from bar

结果是：

foo 1   Hello
bar 2   2018-10-29
foo 3   World
bar 4   2018-10-29

还有一个额外的奖励：

删除序列baz

ERROR: cannot drop sequence baz because other objects depend on it
  Detail: 
    default for table foo column id depends on sequence baz
    default for table bar column id depends on sequence baz
  Hint: Use DROP ... CASCADE to drop the dependent objects too.

编辑：如果我们可以进行后处理，那么这种方法可以用来为缺失的ID列分配值：

update bar
SET id = coalesce((select id from foo where bar.city_name = foo.city_name),nextval('baz'))
WHERE id is null

- Balazs Gunics

---

虽然不完全是我需要的，但我想你指出了正确答案。我目前正在运行update table2 set id = (select nextval('table1_seq1')) where id is null - Luffydude

更新语句如下： UPDATE bar SET id = COALESCE((SELECT id FROM foo WHERE bar.value::text = foo.value), NEXTVAL('baz')) WHERE id IS NULL - Balazs Gunics

table1中的ID已经填好，所以不需要使用coalesce查询，谢谢。等它完成后我会回报 :) - Luffydude

嗯，经过1100分钟后......我的查询更新了table2表，将id设置为（选择nextval（'table1_seq1'））其中id为空。这导致每一行都变成了id 12548435。 - Luffydude

因为 select nextval 只被评估一次，所以我认为你应该使用以下代码： Update table2 set id = nextval('table1_seq1') where id is null - Balazs Gunics

Answer 2

如果您的表已经创建，那么您必须创建一个序列

create sequence seq_city_country;

然后使用以下代码将该序列添加到您的ID中。

ALTER TABLE city ALTER COLUMN id_city SET DEFAULT nextval('seq_city_country');
ALTER TABLE country ALTER COLUMN id_country SET DEFAULT nextval('seq_city_country');

如果您已经通过(sequence_c)为表city创建了序列，您可以使用以下操作：

  ALTER TABLE country ALTER COLUMN id_country SET DEFAULT nextval('sequence_c');

- Kalifornium

Answer 3

CREATE SEQUENCE shared_seq;

CREATE TABLE a (
   col1 bigint  DEFAULT nextval('shared_seq'),
   ...
);

CREATE TABLE b (
   col1 bigint  DEFAULT nextval('shared_seq'),
   ...
);

- krithikaGopalakrishnan

Answer 4

这不像是一个很好的（甚至可能）数据库设计。相反，我建议创建一个关联城市和它们所属国家的连接表。因此，你的三个表可能看起来像这样：

city (PK id, name, ...)
country (PK id, name, ...)
country_city (city_id, country_id)  PK (city_id -> city(id), country_id -> country(id))

使用这种设计，您不需要担心city和country表中的自动增量序列。只需让Postgres分配这些值，然后使用正确的值维护连接表即可。

- Tim Biegeleisen

---

我不理解你的第三个表概念。我的需求是两个表中相同的行共享相同的ID（特别是这些行已经在其他表中使用过），而table2中的新行需要具有不同的ID。 - Luffydude

@Luffydude 很遗憾，你完全没有理解我的回答重点。你的城市和国家表格应该是通过主键和外键关系相关联，而不是通过行序列相关联。 - Tim Biegeleisen

抱歉如果我表达不清楚，序列是table1的主键，而我需要未使用的序列号成为table2的主键。 - Luffydude

是的，这基本上就是我的答案如何工作的。city和country表的两个id列将是自动递增序列。然后联接表的主键将是每个序列中的一个值的组合。 - Tim Biegeleisen

连接表通常非常简洁，因为它们只包含整数ID。添加一个不会破坏您的数据库。 - Tim Biegeleisen