主页 / dba / 问题 / 268194
Accepted
Thirumal
Asked: 2020-05-31 02:11:24 +0800 CST

如何在将主键序列（整数）转换为大序列（大整数）时设置当前序列

  • 772

该表已经有几千行，我想将主键从 Integer 转换为 BigInteger。如何更改大整数的序列？

--Create new temp sequence (sequence-name1) for bigint
CREATE SEQUENCE public.case_audit_case_audit_uid_seq1
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq1  OWNER TO postgres;
--Alter column to bigint
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;  
--Alter pk column with temp Sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT     nextval('indsolv.case_audit_case_audit_uid_seq1'::regclass);
--Drop original sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq;
--Create new sequence for bigint with original sequence name
CREATE SEQUENCE public.case_audit_case_audit_uid_seq
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq OWNER TO postgres;
---Set original sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT nextval('public.case_audit_case_audit_uid_seq'::regclass);
---Drop temp sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq1;

有没有比这更简单的解决方案？

postgresql primary-key

1 个回答

  1. Best Answer

    PgAdmin4 没有以图形方式修改的选项，但根据PostgreSQL 文档，我们可以将序列数据类型更改为 smallint、integer 和 bigint

    更改表列和序列数据类型并为序列设置最大值

    ALTER SEQUENCE public.case_audit_case_audit_uid_seq AS bigint;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq MAXVALUE 9223372036854775807;
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;
    • 1

相关问题