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@mail.gmail.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
