将PostgreSQL列类型从bigint转换为bigserial

Question

当我试图使用alter命令更改表中列的数据类型时...

alter table temp alter column id type bigserial;

我理解了

ERROR:  type "bigserial" does not exist

如何将数据类型从bigint更改为bigserial？

- k s l kumar

Answer 1

正如 文档 中所解释的那样，SERIAL 不是一种数据类型，而是一组其他命令的快捷方式。

因此，虽然您不能通过更改类型来简单地更改它，但您可以通过自己运行这些其他命令来实现相同的效果：

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;

更改所有者将确保如果表/列被删除，则序列会被删除。同时，它也会给予您在pg_get_serial_sequence()函数中期望的行为。

坚持使用tablename_columnname_seq命名约定是必要的，以便使一些工具（如pgAdmin）报告此列类型为BIGSERIAL。请注意，即使列最初声明为SERIAL类型，psql和pg_dump也将始终显示基础定义。

截至Postgres 10，您还可以选择使用SQL标准的identity column，它可隐式处理所有这些，并且可以轻松地添加到现有表中：

ALTER TABLE temp ALTER COLUMN id
  ADD GENERATED BY DEFAULT AS IDENTITY

- Nick Barnes

---

我感觉这并没有真正回答如何在现有列上获得bigserial的问题。你是说在bigint上设置serial会导致它变成bigserial吗？ - Alkanshel

基本上是的。Postgres实际上并不跟踪列是否被创建为SERIAL类型。如果您查看具有BIGSERIAL的表的pg_dump输出，您将只看到一个BIGINT和一个序列（自己创建BIGINT和序列与使用BIGSERIAL关键字完全相同）。需要注意的是，从Postgres 10开始，您可以选择将其转换为身份列。 - Nick Barnes

Answer 2

将列从BIGINTEGER更改为BIGSERIAL以使其自动递增是行不通的。BIGSERIAL不是真正的类型，而是一种可以自动创建PK和SEQUENCE的技巧。

相反，您可以自己创建一个序列，然后将其分配为列的默认值：

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");

- Vao Tsun

---

请注意，这将不会使用现有值填充该列。因此，如果存在空值或非唯一值的现有行，则添加PRIMARY KEY约束将失败。（假设用户尚未具有主键。SERIAL不仅适用于主键，且SERIAL不意味着SERIAL PRIMARY KEY）。此外，如果表中存在行，则需要使用setval序列来确保它从第一个空闲标识符开始，并首先锁定表以防止并发的INSERTs。 - Craig Ringer

Answer 3

这是一个简单的解决方法：

ALTER TABLE table_name drop column column_name, add column column_name bigserial;

- Elias Kabir Noyon

---

正如你所说，这是一个“hack”。他将会失去存储在stat列中的所有数据。不好的解决方案！ - Alexander

@Alexander，你没有完全理解。无论旧值是什么，当有人决定将列类型设置为*serial时，他必须创建并使用序列。如果这样做不起作用，那么可以使用各种方法。可以添加一个额外的列，复制旧值，应用我的技巧，使用旧值更新值，最后通过ALTER SEQUENCE设置适当的nextval()。希望这可以帮到你。 - Elias Kabir Noyon

Answer 4

听起来好像有很多专业人士在这个问题上......如果原始表确实有数据，那么解决这个困境的真正答案是在第一次正确设计数据库。然而，既然如此，更改列规则（类型）将需要验证该列的完整性以适应新范例。而且，不要忘记，任何操作该列的地方（添加/更新）都需要进行检查。

如果这是一个新表，那么可以简单地删除列并重新添加新列（为您处理序列）。同样，设计，设计，设计。

我想我们都犯过这种错误。

- John Smith