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)
signature.asc
Description: This is a digitally signed message part
