postgresql 如何将主键从整数转换为串行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27307835/
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
How to convert primary key from integer to serial?
提问by Damir
In a Postgres 9.3 table I have an integeras primary key with automatic sequence to increment, but I have reached the maximum for integer. How to convert it from integerto serial?
I tried:
在 Postgres 9.3 表中，我有一个integer带有自动递增序列的主键，但我已经达到了integer. 如何将其从 转换integer为serial？
我试过：
ALTER TABLE my_table ALTER COLUMN id SET DATA TYPE bigint;
But the same does not work with the data type serialinstead of bigint. Seems like I cannot convert to serial?
但同样不适用于数据类型serial而不是bigint. 好像我不能转换为serial?
回答by Erwin Brandstetter
serialis a pseudodata type, not an actual data type. It's an integerunderneath with some additional DDL commands executed automatically:
serial是伪数据类型，而不是实际数据类型。它是integer一些自动执行的附加 DDL 命令的底层：
- Create a sequence (with matching name by default).
- Set the column
NOT NULLand the default to draw from that sequence. - Make the column "own" the sequence.
- 创建一个序列（默认名称匹配）。
- 设置列
NOT NULL和默认值以从该序列中绘制。 - 使列“拥有”序列。
Details:
细节：
A bigserialis the same, built around a bigintcolumn. You want bigint, but you already achieved that. To transform an existing serialcolumn into a bigserial(or smallserial), all you need to do is to ALTERthe data type of the column. Sequences are generally based on bigint, so the same sequence can be used for any integertype.
Abigserial是相同的，围绕一bigint列构建。你想要bigint，但你已经做到了。要将现有serial列转换为bigserial(或smallserial)，您需要做的就是更改ALTER列的数据类型。序列通常基于bigint，因此相同的序列可用于任何integer类型。
To "change" a bigintinto a bigserialor an integerinto a serial, you just have to do the rest by hand:
要将 a 更改bigint为 abigserial或 aninteger为 a serial，您只需手动完成其余工作：
The actual data type is still integer/ bigint. Some clients like pgAdmin will display the data type serialin the reverse engineered CREATE TABLEscript, if all criteria for a serialare met.
实际的数据类型仍然是integer/ bigint。如果满足 a 的所有条件，一些客户端（如 pgAdmin）将serial在反向工程CREATE TABLE脚本中显示数据类型serial。
