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

我的表包含可为空的整数列（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命令来进行转换。代码示例将不胜感激。。。

最佳答案

serial只是一个从序列中获取默认值的列。
假设您的表名为n1000，那么下面的命令将满足您的要求。
首先要做的是创建该序列：

create sequence n1000_gid_seq;

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

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

要真正创建“serial”，还需要告诉序列它与列关联：

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;

完成后，可以将列定义为PK：

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

当然，如果您已经关闭了自动提交功能，那么您需要commit所有这些功能。