Re: alter column type

On Fri, Jun 5, 2015 at 2:23 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> 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;
>
>
Thanks for the idea. Since I'm planning to dump the database first anyway
(using Michael's suggestion) I'm thinking I'll try this on the live
database, after I get it safely dumped. It seemed to work on a test
database.

Being unfamiliar with the internals, what's the risk here? If postgres
thinks something is a bigint, but previously stored it as an int, does that
mean it will try to extract data beyond the boundary of some of the (old)
32-bit values and potentially throw off offsets for other values?

Casey