Обсуждение: Change column type from int to bigint - quickest way

Поиск
Список
Период
Сортировка

Change column type from int to bigint - quickest way

От
Andreas Brandl
Дата:
Hi,

we have a pretty big table with an integer-type primary key. I'm looking for the quickest way to change the column type
tobigint to avoid hitting the integer limit. We're trying to avoid prolonged lock situations and full table rewrites. 

I know I can hack this with an UPDATE on pg_attribute:

-- change id type to bigint
update pg_attribute set atttypid=20 where attrelid=264782 and attname = 'id';

After that I'd need to reflect the change on dependent objects like views as well.

Is this safe to do? Are there any unwanted consequences to this?

This is still on 9.1 unfortunately - upgrade is going to follow soon after this.

Thanks!
Andreas


Re: Change column type from int to bigint - quickest way

От
Merlin Moncure
Дата:
On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl <mail@andreas-brandl.de> wrote:
> Hi,
>
> we have a pretty big table with an integer-type primary key. I'm looking for the quickest way to change the column
typeto bigint to avoid hitting the integer limit. We're trying to avoid prolonged lock situations and full table
rewrites.
>
> I know I can hack this with an UPDATE on pg_attribute:
>
> -- change id type to bigint
> update pg_attribute set atttypid=20 where attrelid=264782 and attname = 'id';
>
> After that I'd need to reflect the change on dependent objects like views as well.
>
> Is this safe to do? Are there any unwanted consequences to this?
>
> This is still on 9.1 unfortunately - upgrade is going to follow soon after this.

You just posted the same question a few days ago -- were the answers
there unsatisfactory?

merlin


Re: Change column type from int to bigint - quickest way

От
"David G. Johnston"
Дата:
On Wed, Nov 16, 2016 at 7:49 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl <mail@andreas-brandl.de> wrote:

You just posted the same question a few days ago -- were the answers
there unsatisfactory?

​This seems to be a mail system provoked duplicate since Gmail is telling me this was sent 11/11 but I just received it yesterday.  See my attribution quotes​
 above.

​David J.

Re: Change column type from int to bigint - quickest way

От
Andreas Brandl
Дата:
> On Wed, Nov 16, 2016 at 7:49 AM, Merlin Moncure < mmoncure@gmail.com > wrote:

>> On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl < mail@andreas-brandl.de >
>> wrote:

>> You just posted the same question a few days ago -- were the answers
>> there unsatisfactory?

> This seems to be a mail system provoked duplicate since Gmail is telling me
> this was sent 11/11 but I just received it yesterday. See my attribution
> quotes
> above.

My first post got bounced but re-appeared today - sorry about this.

The answers were very helpful indeed - thanks Tom and Merlin!

We ended up including the datatype change with the upgrade to 9.6. We're going to have a downtime for this anyways and
witha dump-restore-style upgrade this is fairly easy to change. 

Thanks again,
Andreas