I have this (rather ugly, generated) prepared statement to fetch some game data. I try to check if a value ($3) is contained in spawn_level_range
(which is an int4range
) by doing $3<@quests.spawn_level_range
:
SELECT quests.id,
quests.base_attack,
quests.base_strg,
quests.base_accy,
quests.base_hp,
quests.name,
quests.task,
quests.image_url,
quests.spawn_chance
FROM quests
WHERE (((quests.server_id=$1)
AND ((quests.channel_id='all') OR (quests.channel_id=$2)))
AND ($3<@quests.spawn_level_range))
ORDER BY RANDOM()
LIMIT 1;
This exact query works perfectly when pasted into psql
when I prepend:
prepare test (varchar, varchar, int) AS
then run it with:
execute test('669105577238069249', '682205516667158549', 1);
However, for some reason, it just does not work in libpq.
When running the statement with PQexecPrepared
, it raises the error:
ERROR: malformed range literal: "1"
DETAIL: Missing left parenthesis or bracket.
(note that 1
is what I'm trying to bind $3
to)
It seems like it's trying to interpret $3
as a range (rather than an integer
) – which seems like a bug to me.
In your prepared statement, you explicitly declare the third parameter to be an integer
.
In your PQprepare
call (that you didn't show) you must have neglected to set the paramTypes
argument to indicate the types of the parameters, so they are all unknown
to PostgreSQL, and it infers the data type from the context.
Now there are two operators <@
for ranges:
anyrange <@ anyrange
anyelement <@ anyrange
Not knowing which one you want, PostgreSQL's data type resolution rules prefer the operator that has the same data type on both sides.
There are two possible solutions:
paramTypes
argument of PQprepare
CAST ($3 AS integer)