Change column type from int to bigint - quickest way

Andreas Brandl <ml@3.141592654.de> writes:

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

You have no choice. Those types are different sizes so there is no way
to avoid a table rewrite.

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';

That WILL break your table.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Friday, November 11, 2016, Andreas Brandl <ml@3.141592654.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 type to 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.

Thanks!
Andreas

Hm. just thinking out loud:

How about making a new column without default that is bigint and updated
via trigger. then you can over time update the table row by row in batches
over many transactions to initialize the id. once completely set you can
do a swap with some carefully written and tested ddl that will exchange
out the name and any dependent objects such as ri triggers.

The exchanging step ought to be quick. you may have to temporarily disable
ri checks to keep things running smooth.

merlin