BUG #17051: Incorrect params inferred on PREPARE - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #17051: Incorrect params inferred on PREPARE
Date
Msg-id 17051-c20aad26366e11b4@postgresql.org
Whole thread Raw
Responses Re: BUG #17051: Incorrect params inferred on PREPARE
List pgsql-bugs
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.


pgsql-bugs by date:

Previous
From: 甄明洋
Date:
Subject: setting the timezone parameter with space cause diff result
Next
From: PG Bug reporting form
Date:
Subject: BUG #17052: Incorrect params inferred on PREPARE (part 2)