Re: alter column type

CAUTION: This is very dangerous and may cause corruption.
*** DO THIS IN A TEST DATABASE FIRST ***

--1. Get the oid for int8 (bigint)
SELECT t.oid
FROM pg_type t
WHERE typname = 'int8';

--2. Get the oid for your table
SELECT c.oid,
c.relname as table,
a.attname ,
a.atttypid,
a.*
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_attribute a ON ( a.attrelid = c.oid )
WHERE c.relname = 'dogs'
AND n.nspname = 'public'
AND a.attname = 'good_watchdog'
AND NOT attisdropped;

BEGIN;

UPDATE pg_attribute a
SET atttypid = <t.oid from 1>
WHERE a.attrelid = <c.oid from 2>
AND attname = <your column to change>;

COMMIT;

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
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.