Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> A problem shown in https://github.com/psycopg/psycopg/discussions/289
> In the query:
> UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date = $3::date
> passing a string with unknown oid as param $3 results in the column ts
> receiving only the date part. Looks like the cast needed on the param
> in the WHERE gets propagated to the other occurrences of the same
> parameter.
I think it's operating exactly as designed. The parameter can only
have one type, and the cast is telling it to assume that that type
is "date".
> I see why it happens... I don't think it's the right behaviour though.
What do you think ought to happen? The same parameter somehow having
different types in different places?
Based upon:
"When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is first referenced (if possible)."
(PREPARE Command docs)
and:
"""
postgres=# prepare prepupd as update table1 set ts = $1, tsd = $1 returning ts, tsd, pg_typeof($1);
ERROR: inconsistent types deduced for parameter $1
LINE 1: ...epare prepupd as update table1 set ts = $1, tsd = $1 returni...
^
DETAIL: timestamp without time zone versus date
"""
(the above confirming the set clause does provide information for the deduction, ts is timestamp, tsd is date)
> UPDATE test289 SET num = $1, name = $2, ts = $3 WHERE ts::date = $3::date
The parameter would seem to be first referenced as a timestamp (at least in parsing order). As the explicit cast from timestamp to date is valid there is no inconsistency if we don't use the explicit cast for deduction and simply allow the cast to do its thing.
I'm not sure I actually believe this to be an improvement, but I also wasn't expecting that specific error message given the documentation, which at least lets the timestamp deduction stand to get past the assignment of a type to the parameter - letting some other type problem pop up during the rest of the planning or, as in this case, letting the explicit cast actually produce the desired result.
David J.