postgresql - 无法在 postgres 中将整数数据类型转换为 bigserial

Question

为了在 postgres 中将整数数据类型转换为 bigserial，我运行了以下命令，但它没有更改其数据类型，但它正在更改修饰符

CREATE SEQUENCE id;
ALTER TABLE user_event_logs ALTER COLUMN id SET NOT NULL;
ALTER TABLE user_event_logs ALTER COLUMN id SET DEFAULT nextval('id');
ALTER SEQUENCE id OWNED BY user_event_logs.id;

运行后输出结果如下

Column Type  Modifiers  this all my column heading and id  integer  not null 

我想将类型更改为 bigserial

Answer 1

bigserial 是 bigint + sequence + default value 的快捷方式，因此如果您希望 user_event_logs.id 为 bigint，而不是 int ，使用:

ALTER TABLE user_event_logs ALTER COLUMN id type bigint;

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

还有:

The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column.

所以如果你想要bigserial，只需将卷类型更改为bigint