Re: Alter Table Column Datatype - Mailing list pgsql-hackers

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