Mike Christensen <mike@kitchenpc.com> writes:
> Oh. The CREATE CAST command. Wow, I was totally unaware of this entire
> feature!
Before you get too excited about inserting your own implicit casts,
you really should get familiar with the reasons there aren't ones
already ;-).
As was mentioned upthread, we used to have rather more implicit casts than
we do today. We took a bunch of them out in 7.3, and some more in 8.3,
resulting in much gnashing of teeth each time. Some of it from people who
later admitted that the exercise of cleaning up their SQL code had exposed
previously-unrecognized bugs in their applications.
The more or less canonical example of what unrestrained implicit casting
can do to you is here:
http://www.postgresql.org/message-id/b42b73150702191339t71edd1bxa2510df0c4d75876@mail.gmail.com
in which an expression like this
((now() - '1 day'::interval)::timestamp - now()) < 0
behaved in a way not only nonsensical but locale-dependent. Although it
looks sane on first glance, the left side actually produces a result of
type interval, and there is no "interval < integer" operator. Modern
versions of PG tell you so, but what happened pre-8.3 was that the parser
would find the implicit coercions from interval to text and integer to
text, and conclude that it could legally implement this expression by
coercing both sides to text and applying the "text < text" operator.
Well, it ran, but it didn't give the results the user expected.
Variants on the theme can be found throughout our mail archives, at
least up till the pre-8.3 releases dropped out of general use.
Some fun ones I found in a quick troll:
http://www.postgresql.org/message-id/1536369C345BD4118148000629C9833D57EAB5@nifty.preston.traveltech.co.uk
http://www.postgresql.org/message-id/45D4E5A7.9060702@wykids.org
http://www.postgresql.org/message-id/E1Bg5qd-0001E8-00@ms2.city.ac.uk
http://www.postgresql.org/message-id/2793.1037034592@sss.pgh.pa.us
http://www.postgresql.org/message-id/12659.1071876784@sss.pgh.pa.us
The design rule we use now, which seems generally successful at preventing
such surprising behaviors, is to allow a cast to be implicit only when it
is a non-information-losing coercion from one type to another one in the
same basic type category. For instance, integer to numeric is fine
(since, for instance, numeric comparison of two values is generally going
to be consistent with integer semantics). Integer to text, not so much.
In the particular cases being mentioned here, such as enum versus text,
we'd be opening people up to this type of hurt if we added implicit casts,
because you could very well get a textual comparison where you'd expected
an enum-ordering-based comparison. Or vice versa.
So we're not going there; we've already been there, and not liked it.
But you're free to repeat our mistakes if you insist.
regards, tom lane