将主键int类型更改为Serial

问题描述

有没有办法在不删除表的情况下将现有的主键类型从int更改为serial？表格中已经有很多数据，并且我不想删除它。

Is there a way to change existing primary key type from int to serial without dropping the table? I already have a lot of data in the table and I don't want to delete it.

推荐答案

将int转换为串行，从而使其成为一个序列；

Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial;

• 为序列号选择一个起始值，大于表中的任何现有值
  
  SELECT MAX（id ）+1 FROM mytable

为序列创建一个序列（tablename_columnname_seq是一个好名字）

创建序列test_id_seq最小值3 （假设您要从3开始）

Create a sequence for the serial (tablename_columnname_seq is a good name)
CREATE SEQUENCE test_id_seq MINVALUE 3 (assuming you want to start at 3)

更改默认值列以使用序列

ALTER TABLE test ALTER id SET DEFAULT nextval（'test_id_seq'）

Alter the default of the column to use the sequence
ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')

更改表/列所拥有的序列；

ALTER SEQUENCE test_id_seq拥有者test.id

Alter the sequence to be owned by the table/column;
ALTER SEQUENCE test_id_seq OWNED BY test.id

。

和往常一样，养成运行完整备份的习惯
之前，运行更改来自互联网上随机人群的SQL查询；-）

And as always, make a habit of running a full backup before running altering SQL queries from random people on the Internet ;-)

这篇关于将主键int类型更改为Serial的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！