0

The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for 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;

Is there any simple solution compare to this?

4
  • You don't need to change the sequence. It generates bigint values anyway. All you need to do is change the column to bigint
    – user1822
    May 30, 2020 at 10:45
  • No, I am getting an "ERROR: nextval: reached maximum value of sequence "public.case_audit_case_audit_uid_seq" (2147483647)"
    – Thirumal
    May 30, 2020 at 14:32
  • 1
    Then you must have explicitly defined it as an integer sequence, because the default is bigint
    – user1822
    May 30, 2020 at 15:56
  • Yes, I have used EA Sparx System for data modeling and to generate DDL.
    – Thirumal
    May 30, 2020 at 16:21

1 Answer 1

1

PgAdmin4 has no option to modify graphically but as per PostgreSQL Document, we can alter the sequence data type to smallint, integer, and bigint

Alter table column & sequence data type and set max value for the sequence

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;  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.