BUG #17051: Incorrect params inferred on PREPARE

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: arthur(dot)mcgibbon(at)gmail(dot)com
Subject: BUG #17051: Incorrect params inferred on PREPARE
Date: 2021-06-08 09:56:58
Message-ID: 17051-c20aad26366e11b4@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17051
Logged by: Arthur McGibbon
Email address: arthur(dot)mcgibbon(at)gmail(dot)com
PostgreSQL version: 13.3
Operating system: Windows 10 + WSL2 + Docker
Description:

Using the table...

CREATE TABLE testSchema.testTable (timestampCol timestamp);

...and preparing the query...

PREPARE testQuery (unknown) AS
UPDATE testSchema.testTable
SET timestampCol = CASE WHEN timestampCol IS NULL THEN $1 ELSE NULL END;

...results in an error...

ERROR: column "timestampcol" is of type timestamp without time zone but
expression is of type text
LINE 3: set timestampCol = case when timestampCol is null then $1 ...
^
HINT: You will need to rewrite or cast the expression.
SQL state: 42804
Character: 80

Specifying the parameter as timestamp works without error...
PREPARE testQuery (timestamp) AS
UPDATE testSchema.testTable
SET timestampCol = CASE WHEN timestampCol IS NULL THEN $1 ELSE NULL END;

I'd hope that PostgreSQL would infer a "timestamp" type here or reply with
an error that it couldn't infer the parameter type.
Why does it default to type "text" and then effectively say the query is
invalid?

I can cast my parameter in the query to get around this but am submitting
the bug because inferring "text" seems wrong.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2021-06-08 11:27:51 BUG #17052: Incorrect params inferred on PREPARE (part 2)
Previous Message 甄明洋 2021-06-08 08:17:09 setting the timezone parameter with space cause diff result