Type Casting while passing parameters to query

[jturnercode]

Hello,

i am using below:
latest 3.0.11 binary
python 3.10
postgres 13

looking to see if below can be done using passing parameter features

Below works fine while passing raw sql query to cur.execute():
Note the type cast on ts (timestamp to date) to filter qry by date.

qry = """UPDATE "mytable" SET "num"=300, "name"='Fred', "ts"='2020-03-03 12:00:00 WHERE "ts"::date='2020-03-03';"""

The below does not work the same while passing as parameters into built statement:

qry = UPDATE "mytable" SET "num"=%(num)s, "name"=%(name)s, "ts"=%(ts)s
WHERE "ts"::DATE=%(ts)s;

passing dictionary:
data = {
"num": 300,
"name": 'Fred,
"ts": '2022-03-03 12:00:00',
}

cur.execute(query=qry, params=data)
ts updates existing record with say ts='2022-03-03 11:00:00' the time, ie "2022-03-03 00:00:00"

am i doing something wrong or is this unexpected due to type casting?

thank you in advance for help

[dvarrazzo]

I'm not sure I understand correctly what you are asking, but in the second query you are filtering

WHERE "ts"::date='2020-03-03 12:00:00'

What you are doing is pretty ambiguous in terms of types. I think you should specify your conversions more strictly, such as:

WHERE "ts"::DATE=%(ts)s::DATE;

[dvarrazzo]

That's so surprising... but you are actually right. 😳

Test table:

piro=# create table test289 (num int, name text, ts timestamp);
CREATE TABLE
piro=# insert into test289 values (300, 'Fred', '2022-03-03 11:00:00');
INSERT 0 1
piro=# insert into test289 values (200, 'Barney', '2022-03-02 11:00:00');
INSERT 0 1
piro=# select * from test289;
┌─────┬────────┬─────────────────────┐
│ num │  name  │         ts          │
├─────┼────────┼─────────────────────┤
│ 300 │ Fred   │ 2022-03-03 11:00:00 │
│ 200 │ Barney │ 2022-03-02 11:00:00 │
└─────┴────────┴─────────────────────┘
(2 rows)

>>> import psycopg
>>> cnn = psycopg.connect("", autocommit=True)

>>> cnn.execute("update test289 set num = %(num)s, name = %(name)s, ts = %(ts)s where ts::date = %(ts)s",
... {"num": 301, "name": "Fred2", "ts": "2022-03-03 20:00:00"})

piro=# select * from test289;
┌─────┬────────┬─────────────────────┐
│ num │  name  │         ts          │
├─────┼────────┼─────────────────────┤
│ 200 │ Barney │ 2022-03-02 11:00:00 │
│ 301 │ Fred2  │ 2022-03-03 00:00:00 │  <<< ???
└─────┴────────┴─────────────────────┘
(2 rows)

This only happens with PQexecParams and with the unknown type. Using PQexec instead (client-side parameters):

>>> cnn.execute("update test289 set num = '%(num)s', name = '%(name)s', ts = '%(ts)s' where ts::date = '%(ts)s'"
... % {"num": 301, "name": "Fred2", "ts": "2022-03-03 20:00:00"})

or using a Python type that makes the OID specific (note that here a cast on the right of the where is needed, otherwise date gets promoted to a timestamp with time 00:00, so the WHERE won't match any record):

>>> cnn.execute("update test289 set num = %(num)s, name = %(name)s, ts = %(ts)s where ts::date = %(ts)s::date",
... {"num": 301, "name": "Fred2", "ts": dt.datetime.fromisoformat("2022-03-03 20:00:00")})

produces the expected result:

┌─────┬────────┬─────────────────────┐
│ num │  name  │         ts          │
├─────┼────────┼─────────────────────┤
│ 200 │ Barney │ 2022-03-02 11:00:00 │
│ 301 │ Fred2  │ 2022-03-03 20:00:00 │
└─────┴────────┴─────────────────────┘
(2 rows)

It seems a bug in the way the server deals with unknown parameters. Will create a minimal repro to propose to the Postgres people.

I think that specifying the types, as in the second example, is the best workaround for the moment.

[dvarrazzo]

Writing a minimal repro for the Postgres people, I think I understand why the problem happens. For parameters of unknown types, the server finds only once the type, then uses the same type for all the other occurrences.

This suggests other workarounds, such as using ts::date = %(ts)s::timestamp::date in the WHERE condition, or copying the "ts" parameter in two and pass it as value and as filter separately.

Anyway, the ball is in in the server field now.

[jturnercode]

thank you very much for insight to this issue.
i was so stuck

i hope it gets addressed so at end there is consistent behavior
thank you for all hard work on psycopg

[dvarrazzo]

I doubt it will be addressed. A parametric query is seen, in the Postgres model, as a function whose parameters can only have one single type. The literal replacement of values to placeholders is more similar to a macro preprocessor.

What I find confusing in this issue is that the type of the parameter is chosen as a date, and that's only for the implementation of detail of how the query tree is analysed. I would have preferred an error to an arbitrary result.

I think you should stay on the safe side and specify Python types rather than using strings, which leaves the interpretation of the types to the server and opens to ambiguities.

[jturnercode]

the auto-interpretation/conversion of types was nice for short stint that it lasted (started learning more sql and postgres).
i was surprised that this has not come up before as i find it to be very common scenario for me

I collect data for over 2000 devices throughout the day and record the latest timestamp that the device currently shows
when i looked up how to update a record by timestamp column the submitted "ts::date = %(ts)s"(raw sql equivalent) seemed to be suggested route
it did not make complete sense and had a love/hate with ambiguity

your suggestion makes more sense "ts::date = %(ts)s::timestamp::date" as it is more explicit.
At end i thought the underlining raw sql working was based on something like in the what is seen in 'on conflict' clause where a 'EXCLUDED' table is created to preserve values (my understanding of it)--by the way i have no issues in my upsert function while passing the same parameters

yet now finding out it is related to query tree order for 'update' is a bit surprising for what i find common scenario

thank you for work around