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) 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)
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 do
UPDATE 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