While datatypes are optional in prepared statements, not using them can results in type errors such as
function sum(text) does not exist
This is easily reproducible in any PSQL client
PREPARE my_query AS
SELECT SUM(CASE WHEN v = 'A' THEN $1 ELSE $2 END)
FROM (VALUES ('A'), ('B')) as q0(v);
-- ERROR: function sum(text) does not exist
But when I specify the types, it works
PREPARE my_query(int, int) AS
SELECT SUM(CASE WHEN v = 'A' THEN $1 ELSE $2 END)
FROM (VALUES ('A'), ('B')) as q0(v);
EXECUTE my_query(1,0);
-- 1
DEALLOCATE my_query;
asyncpg currently does not include types, which causes me to run into the sum(text) error. I'd expect it to either include types, or allow me to specify the types somehow.
Workaround:
Include a cast expression.
i.e.: $1::int or CAST($1 AS INT)
Full example
PREPARE my_query AS
SELECT SUM(CASE WHEN v = 'A' THEN $1::int ELSE $2::int END)
FROM (VALUES ('A'), ('B')) as q0(v);
While datatypes are optional in prepared statements, not using them can results in type errors such as
This is easily reproducible in any PSQL client
But when I specify the types, it works
asyncpg currently does not include types, which causes me to run into the
sum(text)
error. I'd expect it to either include types, or allow me to specify the types somehow.Workaround: Include a cast expression. i.e.:
$1::int
orCAST($1 AS INT)
Full example