Re: inconsistent automatic casting between psql and function

Stefano Buliani wrote:
> Richard,
>
> understand I shouldn't be comparing a date to a timestamp. Fact is I
> need the full timestamp to process other info in the rest of the function.
>
> My question is: why is the planner casting the timestamp to date when I
> run the query from psql and the other way around from the function?

It's not. As I said, a quoted literal isn't necessarily a timestamp.

This: '2008-12-09 18:23:00' is not a timestamp.

It is an untyped quoted literal that contains something I'll grant
*looks* like a timestamp, but we can't tell what it is really supposed
to be until it's used.

SELECT length('2008-12-09 18:00:00');

Here it must be text (because we don't have a length() defined for
timestamps - see \df length).

=> SELECT date_trunc('month', '2008-12-09 18:00:00');
ERROR: function date_trunc(unknown, unknown) is not unique
LINE 1: SELECT date_trunc('month', '2008-12-09 18:00:00');
^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.

Here it couldn't decide (\df date_trunc to see what it was choosing between)

And in the next one it guesses it has an interval (because that's what
the other thing is, I'm guessing).

=> SELECT '2008-12-09 18:00:00' + '2 hours'::interval;
ERROR: invalid input syntax for type interval: "2008-12-09 18:00:00"

So - it's not casting from timestamp to date, it's casting from
"unknown" to date in your interactive sql.

--
Richard Huxton
Archonet Ltd