Mike Christensen <mike@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx http://www.postgresql.org/message-id/45D4E5A7.9060702@xxxxxxxxxx http://www.postgresql.org/message-id/E1Bg5qd-0001E8-00@xxxxxxxxxxxxxx http://www.postgresql.org/message-id/2793.1037034592@xxxxxxxxxxxxx http://www.postgresql.org/message-id/12659.1071876784@xxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
