Re: change column data type of a big table

On 08/10/2012 12:05 AM, John R Pierce wrote:
> On 08/09/12 10:31 PM, Anibal David Acosta wrote:
>>
>> I have a very big table, in fact only this table uses approx. 60%
>> space of disk.
>>
>> The table is an standalone table (no one inherit from this and this is
>> not inherit from another).
>>
>> I need to change a int column to bigint.
>>
>> But aparentely this alter recreate the table, my problem is the space,
>> there are no space enough in disk.
>>
>> Is possible to change column datatype without recreating the table?
>>
>>
>
> every tuple of this table will have to be rewritten with the new type.
> you can't avoid that.   as none of the old tuples can be reclaimed
> before the ALTER TABLE completes, you'll need sufficient disk space for
> the old and new data.
>
> I see no way of avoiding needing more disk space.    if you have
> sufficient space on another drive, you could dump the table data-only,
> then truncate it, then alter the type while its empty, then restore the
> dump.
>
>
>
>
Seems like
for i == 1 to 10
select into table-with-bigint from table-with-int where <id mod 10 = i>
delete from table-with-bigint where <id mod 10 = i>

or any such fractionation scheme might do the trick

And obviously more disk is inevitably required unless this table is
frozen, which doesn't seem likely.