On Fri, Jun 5, 2015 at 2:23 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:
CAUTION: This is very dangerous and may cause corruption.COMMIT;
*** 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>;
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
