>Farid Zidan <fa...@zidsoft.com> wrote: > but when it gets to use the resultset of the subquery in the > insert it "forgets" how to convert '2010-04-30 00:00:00' to > timestamp value Not really. In versions prior to 8.3 it did automagically convert like that. PostgreSQL has some pretty fancy features involving custom data types where this magic caused problems, so a deliberate decision was taken to no longer provide automatic casts from text to other data types. > (but forgets only when 'distinct' is used in the subquery!) That is because (as I tried to explain earlier, but apparently didn't do a good job of communicating), an unadorned literal in single quotes is *not* taken to be a character string in PostgreSQL. Its type is held as "unknown" until it is forced to be resolved in some operation. This allows easier coding of custom data types, but does create a few deviations from standard behavior in corner cases, and breaks from the non-standard "conventional" behavior of many other databases. Because of this design choice, for example, the FAA can more easily write the code they use to map their runways and other airport facilities. The cost is that in situations such as you describe, you need to force the type before it is used in the comparisons necessary to determine a distinct value. The only way to get the behavior you want without breaking a great many useful cases, would be to determine where the result was going to be later used, and use that information to force the type to something other than text (the default, when no other information is available). That would be a *major* and destabilizing change. For those reasons, the chance of getting *anybody* here to consider this a bug are close to nil. The choice to more conveniently handle advanced cases at the expense of occasionally needing to specify a type is unlikely to be reversed, to put it mildly.
I can't help but wonder why you resist using the standard syntax. The reason the standard exists is to help those trying to write portable code, so they don't have to count on the vagaries of "parallel evolution." -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
