| 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: | Whole Thread | Raw Message | 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.
| 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 |