Re: alter column type

On Fri, Jun 5, 2015 at 12:23 PM, Casey Deccio <casey(at)deccio(dot)net> wrote:

> I have a database in which one table references the primary key of
> another. The type of the primary key was initially int, but I changed it
> to bigint. However, I forgot to update the type of a column that
> references it. So, I've initiated "ALTER TABLE foo ALTER COLUMN bar TYPE
> bigint", where foo/bar is the table/column referencing the primary key that
> is now of type bigint.
>
> However, with 2^31 rows, it is taking a "long" time to write the rows
> (it's been 12 hours). Is there a more efficient way to do this? Even
> if/when this one finishes, there are other column types that I have to
> update. This update effectively locked me out of all access to the data
> anyway, so I don't foresee any concern of writes that might affect
> integrity.
>
> Cheers,
> Casey
>

Probably too late for this time, but in the past when I've needed to
redefine the type for a column, I've made a dump, edited the dump file to
change the type and then renamed the table and reloaded it. That's usually
several orders of magnitude faster.
--
Mike Nolan
nolan(at)tssi(dot)com