postgresql 将主键 int 类型更改为串行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23578427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing primary key int type to serial
提问by MarisP
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 更改为 serial ？我已经在表中有很多数据，我不想删除它。
回答by Joachim Isaksson
Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial;
将 int或多或少地转换为序列仅意味着将序列默认值添加到 value，从而使其成为序列；
Pick a starting value for the serial, greater than any existing value in the table
SELECT MAX(id)+1 FROM mytableCreate 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 the default of the column to use the sequence
ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')Alter the sequence to be owned by the table/column;
ALTER SEQUENCE test_id_seq OWNED BY test.id
为序列选择一个起始值，大于表中的任何现有值
SELECT MAX(id)+1 FROM mytable为serial创建一个序列（tablename_columnname_seq是个好名字）
CREATE SEQUENCE test_id_seq MINVALUE 3（假设你想从3开始）更改列的默认值以使用序列
ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')更改表/列拥有的序列；
ALTER SEQUENCE test_id_seq OWNED BY test.id
And as always, make a habit of running a full backup beforerunning altering SQL queries from random people on the Internet ;-)
和往常一样，在运行来自 Internet 上随机人员的更改 SQL 查询之前，请养成运行完整备份的习惯；-)
回答by wildplasser
-- temp schema for testing
-- ----------------------------
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE bagger
( id INTEGER NOT NULL PRIMARY KEY
, tralala varchar
);
INSERT INTO bagger(id,tralala)
SELECT gs, 'zzz_' || gs::text
FROM generate_series(1,100) gs
;
DELETE FROM bagger WHERE random() <0.9;
-- SELECT * FROM bagger;
-- CREATE A sequence and tie it to bagger.id
-- -------------------------------------------
CREATE SEQUENCE bagger_id_seq;
ALTER TABLE bagger
ALTER COLUMN id SET NOT NULL
, ALTER COLUMN id SET DEFAULT nextval('player_id_seq')
;
ALTER SEQUENCE bagger_id_seq
OWNED BY bagger.id
;
SELECT setval('bagger_id_seq', MAX(ba.id))
FROM bagger ba
;
-- Check the result
-- ------------------
SELECT * FROM bagger;
\d bagger
\d bagger_id_seq
