PostgreSQL主键ID数据类型从串行到大串行?

Question

我做了一些研究，但找不到我想要的确切答案。目前我有一个设置为串行的主键列"id"，但我想将其更改为 bigserial 以映射到 Java 层中的 Long。考虑到这是现有表，实现此目的的最佳方法是什么？我认为我的 Postgres 版本是 10.5。我也知道串行和大序列都不是一种数据类型。

Answer 1

在Postgres 9.6或更早版本中，由serial列创建的序列已经返回bigint。您可以使用psql进行检查：

drop table if exists my_table;
create table my_table(id serial primary key, str text);
d my_table
Table "public.my_table"
Column |  Type   | Collation | Nullable |               Default                
--------+---------+-----------+----------+--------------------------------------
id     | integer |           | not null | nextval('my_table_id_seq'::regclass)
str    | text    |           |          | 
Indexes:
"my_table_pkey" PRIMARY KEY, btree (id)

d my_table_id_seq
Sequence "public.my_table_id_seq"
Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.my_table.id

因此，您应该只更改串行列的类型：

alter table my_table alter id type bigint;

---

该行为在Postgres 10中发生了变化：

此外，为 SERIAL 列创建的序列现在生成 32 位宽的正值，而以前的版本生成 64 位宽的值。如果值仅存储在列中，则这不会产生可见效果。

因此，在 Postgres 10+ 中：

alter sequence my_table_id_seq as bigint;
alter table my_table alter id type bigint;

Answer 2

-- backup table first
CREATE TABLE tablenamebackup as select * from tablename ;
--add new column idx
alter table tablename add column idx bigserial not null;
-- copy id to idx 
update tablename set idx = id ;
-- drop id column
alter table tablename drop column id ;
-- rename idx to id 
alter table tablename rename column idx to id ;
-- Reset Sequence to max + 1
SELECT setval(pg_get_serial_sequence('tablename', 'id'), coalesce(max(id)+1, 1), false) FROM tablename ;