Thread: Alter Table Column Datatype
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)
Createa 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
wouldreplace 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
becast, the transaction is aborted. This is done with a relfileswap method so vacuum isn't necessary after
thisoperation -- it also cleans out dropped column data at the same time. 5) Drop the old (original)
columnthat 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
wouldallow data to be munged without telling the user about it. In order to correct the Var nodes in
Checkconstraints, views, etc. I need to change the varattno and type information, then wrap it in a cast
tobring 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)
Rod Taylor schrieb:
> Is this syntax ok?
> ALTER TABLE <table> ALTER COLUMN <column> TYPE <new
> type>
shouldn't that be ALTER TABLE <table> ALTER [COLUMN] <column> [TYPE] <new type>
which I clearly remember from different database systems.
Christof
A. On Mon, 2003-09-29 at 10:28, Christof Petig wrote: > Rod Taylor schrieb: > > Is this syntax ok? > > ALTER TABLE <table> ALTER COLUMN <column> TYPE <new > > type> > > shouldn't that be > ALTER TABLE <table> ALTER [COLUMN] <column> [TYPE] <new type> > which I clearly remember from different database systems. I should be able to do that. Thanks.
Rod Taylor <rbt@rbt.ca> writes:
> Questions:
> Is this syntax ok?
> ALTER TABLE <table> ALTER COLUMN <column> TYPE <new
> type>
It should be the same as whatever All Them Other Databases use.
> 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.
I think there needs to be a way to allow explicit coercions; perhaps
even supply a conversion expression if the user wants a transformation
that doesn't correspond to the default coercion. When you are doing
this by hand, you doUPDATE tab SET newcol = oldcol
which can easily accommodate any arbitrary expression on oldcol.
Perhaps the ALTER command could include an optional clause "TRANSFORM
expr-on-oldcol" to do this. In that case, defaulting to assignment
coercion would be fine with me.
> 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?
I think a more likely way of making the conversion is to deparse the
constraint expression to text, then reparse using the new column
definition. For example, if you were promoting an integer column to
numeric, and you had a constraint "x > 0", you'd probably be pretty
surprised if the constraint became "x::int > 0". I think you'd want it
interpreted as numeric "x > 0".
Not sure to what extent we can apply that theory to views, though.
If it would result in a change in a view's output column datatype,
do we want to allow that (which would mean recursively propagating
that column type change to yet other places)?
> Is there any way of expiring a function cache or, for that
> matter, telling which functions use the column in question
> internally?
Not at the moment. Sooner or later we will have to address that issue...
regards, tom lane
> Perhaps the ALTER command could include an optional clause "TRANSFORM
> expr-on-oldcol" to do this. In that case, defaulting to assignment
> coercion would be fine with me.
This would certainly be interesting and shouldn't take too much to
accomplish for simple expressions. If we allow the full expression set
(subselects, multiple columns, etc.) it could get tricky.
> > 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?
>
> I think a more likely way of making the conversion is to deparse the
> constraint expression to text, then reparse using the new column
> definition. For example, if you were promoting an integer column to
> numeric, and you had a constraint "x > 0", you'd probably be pretty
> surprised if the constraint became "x::int > 0". I think you'd want it
> interpreted as numeric "x > 0".
What about the cases where implicit coercion would break the expression
due to type mismatch, but a cast wouldn't?
If they supply the transform clause we would want to inject the opposite
of it into the expression. Of course, that isn't practical, but do we
allow them to provide a DETRANSFORM clause?
Column is boolean.
ALTER TABLE ... COLUMN col TYPE text TRANSFORM CASE WHEN col IS TRUE THEN 'STATE1' ELSE 'STATE2'
END DETRANSFORM CASE WHEN col = 'STATE1' THEN TRUE ELSE FALSE END;
This would do a data conversion to text, and inject the DETRANSFORM case
statement into expressions (like check and default) allowing their use
to continue?
For a first cut, I'll do simple deparse / reparse and allow a simple
TRANSFORM expression (no subselects, only the current column). The most
useful case at the moment is foreign key mismatch and conversion of
default datatypes into domains.
Malformed (after deparse / reparse) check constraints would reject the
transformation.
> Not sure to what extent we can apply that theory to views, though.
> If it would result in a change in a view's output column datatype,
> do we want to allow that (which would mean recursively propagating
> that column type change to yet other places)?
It would be nice to be able to do this at some point, but I'll leave
views and other complex objects as unsupported (throw an error) when
they attempt.
Tom Lane <tgl@sss.pgh.pa.us> writes: > Not sure to what extent we can apply that theory to views, though. > If it would result in a change in a view's output column datatype, > do we want to allow that (which would mean recursively propagating > that column type change to yet other places)? Just as a datapoint: In oracle I think this is one of the things that cause the views become invalid and need to be "recompiled". I always found that to be *extremely* annoying. It would be much nicer if they automatically did the right thing. -- greg
Rod Taylor <rbt@rbt.ca> writes:
>> Perhaps the ALTER command could include an optional clause "TRANSFORM
>> expr-on-oldcol" to do this. In that case, defaulting to assignment
>> coercion would be fine with me.
> This would certainly be interesting and shouldn't take too much to
> accomplish for simple expressions. If we allow the full expression set
> (subselects, multiple columns, etc.) it could get tricky.
Sure, we restrict the expression as much as needed to make it simple to
implement. The objective of this feature is to cover the easy cases,
after all --- if you want something complicated, you can fall back to
manual methods.
>> I think a more likely way of making the conversion is to deparse the
>> constraint expression to text, then reparse using the new column
>> definition. For example, if you were promoting an integer column to
>> numeric, and you had a constraint "x > 0", you'd probably be pretty
>> surprised if the constraint became "x::int > 0". I think you'd want it
>> interpreted as numeric "x > 0".
> What about the cases where implicit coercion would break the expression
> due to type mismatch, but a cast wouldn't?
[ shrug... ] What about the cases where the reverse cast doesn't exist,
or loses information (which was the real point of my example)?
In complicated cases, I think people will have to review their
constraints manually, and perhaps drop the original constraint, change
column type, re-add the appropriately rewritten constraint. What
I think the automatic conversion should try to do is add the least
surprising interpretation of the constraint for the new datatype.
I don't think that casting back to the original datatype is the least
surprising interpretation. Admittedly, it's a judgment call --- but
*whatever* we do will be wrong in some cases.
> If they supply the transform clause we would want to inject the opposite
> of it into the expression. Of course, that isn't practical, but do we
> allow them to provide a DETRANSFORM clause?
I doubt it --- as I said, I don't think that reversing the
transformation in order to keep the exact original semantics of the
constraint is really what people will want on average. If they wanted
the original semantics of the column type, they'd not be needing to
change type, no?
> Column is boolean.
> ALTER TABLE ... COLUMN col TYPE text
> TRANSFORM CASE WHEN col IS TRUE THEN 'STATE1'
> ELSE 'STATE2'
> END
> DETRANSFORM CASE WHEN col =3D 'STATE1' THEN TRUE
> ELSE FALSE
> END;
> This would do a data conversion to text, and inject the DETRANSFORM case
> statement into expressions (like check and default) allowing their use
> to continue?
But why are they bothering to make the transformation? Probably because
they now need more than two states. As such, all their existing
constraints etc will need work anyway.
> Malformed (after deparse / reparse) check constraints would reject the
> transformation.
Right. Also make sure you install the constraint on the new column
before transferring data, so that if it fails for any converted data,
the whole operation aborts.
> It would be nice to be able to do this at some point, but I'll leave
> views and other complex objects as unsupported (throw an error) when
> they attempt.
Sounds reasonable for a first cut.
regards, tom lane