postgresql - 如何填充可为空的整数列并将其转换为 Postgresql 中的串行主键？

Question

我的表包含一个可以为 null 的整数列 (gid):

 gid | value
-------------
  0  |   a
     |   b
  1  |   c
  2  |   d
     |   e

现在我想将 gid 列更改为 SERIAL 主键列。这意味着用新整数填充空槽。现有整数必须保留在原位。所以结果应该是这样的:

 gid | value
-------------
  0  |   a
  3  |   b
  1  |   c
  2  |   d
  4  |   e

我只是想不出执行转换的正确 SQL 命令。代码示例将不胜感激...

Answer 1

serial“只是”一个从序列中获取默认值的列。假设您的表名为 n1000，那么下面的代码将执行您想要的操作。

您需要做的第一件事是创建该序列:

create sequence n1000_gid_seq;

然后您需要将其设置为该列的“默认值”:

alter table n1000 alter column gid set default nextval('n1000_gid_seq');

要真正创建一个“序列”，您还需要告诉序列它与列相关联:

alter sequence n1000_gid_seq owned by n1000.gid;

然后您需要推进序列，以便下一个值不会与现有值冲突:

select setval('n1000_gid_seq', (select max(gid) from n1000), true);

最后您需要更新表中缺失的值:

update n1000 
  set gid = nextval('n1000_gid_seq')
where gid is null;

完成后，您可以将列定义为主键:

alter table n1000 
   add constraint pk_n1000
   primary key (gid);

当然，如果您关闭了自动提交，您需要提交所有这些。