From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: arthur.mcgibbon@gmail.com
Subject: BUG #17051: Incorrect params inferred on PREPARE
Date: Tue, 08 Jun 2021 09:56:58 +0000
Message-ID: <17051-c20aad26366e11b4@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, arthur.mcgibbon@gmail.com
Subject: Re: BUG #17051: Incorrect params inferred on PREPARE
In-Reply-To: <17051-c20aad26366e11b4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox