Skip site navigation (1) Skip section navigation (2)

Alter Table Column Datatype

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Alter Table Column Datatype
Date: 2003-09-29 03:26:01
Message-ID: 1064805960.60248.24.camel@jester (view raw or flat)
Thread:
Lists: pgsql-hackers
I have a few questions (below).

Mechanism:
        1) Rename the old column to ...pg.dropped... to get it out of
        the way of step 2.
        
        2) Create a new column with the wanted type and appropriate
        constraints. Only not null is supported at the moment.
        
        3) Alter in the corrected default (wrapped in cast). It isn't
        done with column definition since that would replace NULLS with
        the default.
        
        4) Copy data from old column to new column with cast.  As you
        can see below it works with domains with constraints (most
        complex case I could come up with.  If data cannot be cast, the
        transaction is aborted. This is done with a relfileswap method
        so vacuum isn't necessary after this operation -- it also cleans
        out dropped column data at the same time.
        
        5) Drop the old (original) column that had earlier been renamed.


Questions:
        Is this syntax ok?
                ALTER TABLE <table> ALTER COLUMN <column> TYPE <new
                type>
        
        COERCE_ASSIGNMENT is the appropriate coercion technique, I
        assume? I don't like EXPLICIT as it would allow data to be
        munged without telling the user about it.
        
        In order to correct the Var nodes in Check constraints, views,
        etc. I  need to change the varattno and type information, then
        wrap it in a cast to bring it back to the old data type?
        
        Is there any way of expiring a function cache or, for that
        matter, telling which functions use the column in question
        internally?


-- Example of work completed to date
rbt=# create domain bdom as integer check(value < 3);
CREATE DOMAIN
rbt=# create sequence bseq;
CREATE SEQUENCE
rbt=# create table btab (col bigint default nextval('bseq'));
CREATE TABLE
rbt=# \d btab
               Table "public.btab"
Column |  Type  |           Modifiers
--+--+--
col    | bigint | default nextval('bseq'::text)

rbt=# insert into btab default values;
INSERT 2509216 1
rbt=# insert into btab default values;
INSERT 2509217 1
rbt=# insert into btab default values;
INSERT 2509218 1
rbt=# insert into btab default values;
INSERT 2509219 1
rbt=# select * from btab;
col
--
   1
   2
   3
   4
(4 rows)

rbt=# alter table btab alter column col type bdom;
ERROR:  value for domain bdom violates CHECK constraint "$1"
rbt=# delete from btab where col >= 3;
DELETE 2
rbt=# alter table btab alter column col type bdom;
ALTER TABLE
rbt=# \d btab
                       Table "public.btab"
Column | Type |                    Modifiers
--+--+--
col    | bdom | default ((nextval('bseq'::text))::integer)::bdom

rbt=# select * from btab;
col
--
   1
   2
(2 rows)

rbt=# insert into btab default values;
ERROR:  value for domain bdom violates CHECK constraint "$1"
rbt=# alter sequence bseq restart with 1;
ALTER SEQUENCE
rbt=# insert into btab default values;
INSERT 2509230 1
rbt=# select * from btab;
col
--
   1
   2
   1
(3 rows)

Responses

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2003-09-29 03:48:48
Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)
Previous:From: Bruce MomjianDate: 2003-09-29 03:16:48
Subject: Re: pg_dump bug in 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group