Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

>Farid Zidan <farid(at)zidsoft(dot)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