SQL PostgreSQL 从 bigint 到 bigserial 的列类型转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31965506/
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
PostgreSQL column type conversion from bigint to bigserial
提问by k s l kumar
When I try to change the data type of a column in a table by alter command...
当我尝试通过 alter 命令更改表中列的数据类型时...
alter table temp alter column id type bigserial;
I get
我得到
ERROR: type "bigserial" does not exist
How can I change the datatype from bigint to bigserial?
如何将数据类型从 bigint 更改为 bigserial？
回答by Nick Barnes
As explained in the documentation, SERIALis not a datatype, but a shortcut for a collection of other commands.
如文档中所述， ,SERIAL不是数据类型，而是其他命令集合的快捷方式。
So while you can't change it simply by altering the type, you can achieve the same effect by running these other commands yourself:
因此，虽然您不能简单地通过更改类型来更改它，但您可以通过自己运行这些其他命令来实现相同的效果：
CREATE SEQUENCE temp_id_seq;
ALTER TABLE temp ALTER COLUMN id SET NOT NULL;
ALTER TABLE temp ALTER COLUMN id SET DEFAULT nextval('temp_id_seq');
ALTER SEQUENCE temp_id_seq OWNED BY temp.id;
Altering the owner will ensure that the sequence is removed if the table/column is dropped. It will also give you the expected behaviour in the pg_get_serial_sequence()function.
更改所有者将确保在删除表/列时删除序列。它还将为您提供pg_get_serial_sequence()函数中的预期行为。
Sticking to the tablename_columnname_seqnaming convention is necessary to convince some tools like pgAdmin to report this column type as BIGSERIAL. Note that psql and pg_dump will always show the underlying definition, even if the column was initially declared as a SERIALtype.
必须坚持tablename_columnname_seq命名约定才能说服 pgAdmin 等工具将此列类型报告为BIGSERIAL. 请注意 psql 和 pg_dump 将始终显示底层定义，即使该列最初声明为SERIAL类型。
As of Postgres 10, you also have the option of using an SQL standard identity column, which handles all of this invisibly, and which you can easily add to an existing table:
从 Postgres 10 开始，您还可以选择使用 SQL 标准标识列，它可以无形地处理所有这些，并且您可以轻松地将其添加到现有表中：
ALTER TABLE temp ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY
回答by Vao Tsun
ALTERing a column from BIGINTEGERto BIGSERIALin order to make it auto-increment won't work. BIGSERIALis not a true type, it is a trick that automates PKand SEQUENCEcreation.
ALTER将一列从BIGINTEGER到BIGSERIAL以使其自动递增是行不通的。BIGSERIAL不是真正的类型，它是一种自动化PK和SEQUENCE创建的技巧。
Instead you can create a sequence yourself, then assign it as the default for a column:
相反，您可以自己创建一个序列，然后将其指定为列的默认值：
CREATE SEQUENCE "YOURSCHEMA"."SEQNAME";
ALTER TABLE "YOURSCHEMA"."TABLENAME"
ALTER COLUMN "COLUMNNAME" SET DEFAULT nextval('"YOURSCHEMA"."SEQNAME"'::regclass);
ALTER TABLE "YOURSCHEMA"."TABLENAME" ADD CONSTRAINT pk PRIMARY KEY ("COLUMNNAME");
回答by Elias Kabir Noyon
This is a simple workaround:
这是一个简单的解决方法：
ALTER TABLE table_name drop column column_name, add column column_name bigserial;
回答by John Smith
Sounds like alot of professionals out there on this subject... if the original table did indeed have data then the real answer to this dilemma is to have designed the db correctly in the first place. However, that being the case, to change the column rule (type) would require integrity verification of that column for the new paradigm. And, don't forget, anywhere where that column is manipulated (added/updated) then that would need to be looked into.
听起来很多专业人士都在讨论这个问题……如果原始表确实有数据，那么解决这个难题的真正答案是首先正确设计数据库。但是，在这种情况下，要更改列规则（类型）将需要针对新范式对该列进行完整性验证。而且，不要忘记，在该列被操作（添加/更新）的任何地方，都需要进行调查。
If it's a new table then okay, simples: delete column and re-add new column (takes care of the sequence for you). Again, design, design, design.
如果它是一个新表，那么可以，简单：删除列并重新添加新列（为您处理序列）。再次，设计，设计，设计。
I think we've all fouled on this.
我想我们都在这件事上犯规了。
