Re: Reconsidering the behavior of ALTER COLUMN TYPE

On Thu, Jun 11, 2015 at 03:41:49PM -0500, Merlin Moncure wrote:
> On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Specifically, it's not clear why you can change the type of a uuid[]
> > column with
> >
> > alter table t alter u type text[];
> >
> > but then you can't change it back with
> >
> > alter table t alter u type uuid[];
> >
> > The reason of course is that uuid-to-text is considered an
> > assignment-grade coercion while text-to-uuid is not.

> > Specifically, after a bit of thought, I suggest that
> >
> > (1) If there's no USING, attempt to coerce the column value as though
> > an *explicit* coercion were used.

> > This could be documented as "if there is no USING, the default behavior
> > is as if you'd written USING column::newtype".

This removes the training wheels from varchar(N) in particular, and
potentially from other three-argument cast functions:

[local] test=# create table t (c) as select 1234;
SELECT 1
[local] test=# alter table t alter c type varchar(2);
ERROR: value too long for type character varying(2)
[local] test=# alter table t alter c type varchar(2) using c::varchar(2);
ALTER TABLE
[local] test=# table t;
c
----
12
(1 row)

I suppose you could accept explicit-only casts yet pass "false" for the
isExplicit argument, but that is a wart.

> > In any case, we oughta use two different error messages for the two cases,
> > as per my comment in the above thread. That seems like a back-patchable
> > bug fix, though of course any semantics change should only be in HEAD.
>
> I have a slight preference to keep it to tightening up the wording on
> both the hint and the error (for example, "Perhaps you meant USING
> foo::type?") but leaving the behavior alone.

+1. The HINT could certainly provide situation-specific help.