The following bug has been logged on the website:
Bug reference: 17051
Logged by: Arthur McGibbon
Email address: arthur.mcgibbon@gmail.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.