From: | Michael Guissine <mguissine(at)gmail(dot)com> |
---|---|
To: | srinivasan s <srinioracledba7(at)gmail(dot)com> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Change column datatype from INT to BIGINT |
Date: | 2024-03-11 17:33:36 |
Message-ID: | CACxDrAnUg9gCrrbZqopyA3QucNrwMyB7caLNDTE-u17J5WxTDw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
I guess it depends on how far you are from the column reaching its max
value and if it's also a primary key on the table or not.
Assuming you still have enough time for this conversion:
If column is NOT primary key:
1. Ensure you have enough space in your database
2. Add a new bigint column
3. Add a trigger to populate a new column from the original as the changes
come in
4. Back populate new column for the existing records
5. Take a small downtime window:
a) rename old column to something like column_old
b) rename new column as old column
6. validate and drop old column
If the column is also a Primary key, there are few more steps required
before you can swap
~ Michael
On Mon, Mar 11, 2024 at 1:12 PM srinivasan s <srinioracledba7(at)gmail(dot)com>
wrote:
> Hi everyone
>
> Hope you’re doing good!
>
> We need to change the data type of one of the columns in the large table
> from INT to BIGINT because it reaches the maximum integer value. What is
> the best way to do this conversion from INT to BIGINT with no downtime or
> very minimal impact, I look forward to receiving help from the experts
> here. Thanks
>
From | Date | Subject | |
---|---|---|---|
Next Message | kumar victor | 2024-03-12 00:51:35 | Re: Advanced DBA Training |
Previous Message | srinivasan s | 2024-03-11 17:11:53 | Change column datatype from INT to BIGINT |