Reconsidering the behavior of ALTER COLUMN TYPE

A recent thread in pgsql-general shows yet another user who's befuddled by
the need to add a USING clause to an ALTER TABLE ... ALTER COLUMN TYPE
command:

http://www.postgresql.org/message-id/flat/CAD25zGDiaqCG=eqXp=byvZcdgvcquBh7KBpJpJQseSPOwfvhiw(at)mail(dot)gmail(dot)com

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.

I've lost count of the number of times we've had to tell someone to
use a USING clause for this. Maybe it's time to be a little bit less
rigid about this situation, and do what the user obviously wants rather
than make him spell out a rather pointless USING.

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.

(2) If there is a USING, maintain the current behavior that the result
has to be assignment-coercible to the new column type. We could use
explicit-coercion semantics here too, but I think that might be throwing
away a bit too much error checking, in a case where the odds of a typo
are measurably higher than for the default situation.

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

Thoughts?

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.

regards, tom lane