You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
//userid is pgx.NullInt32//count is int//offset is int//if userid.Valid is false this should evaluate to NULL is NULL, and thus not filter on userid rows, err:=dbPool.Query(`SELECT id, userid, timestamp, downloadsize FROM videos WHERE ($1 IS NULL OR userid=$1) LIMIT $2 OFFSET $3`, userid, count, offset)
Table videos looks like:
id uuid
userid integer
timestamp timestamp without timezone
downloadsize integer
But this returns the error could not determine data type of parameter $1 (SQLSTATE 42P08). The error occurs both when userid.Valid is true and when false.
What am I doing wrong?
The text was updated successfully, but these errors were encountered:
The problem is $1 IS NULL. pgx will use prepared statements under the hood. When preparing that statement PostgreSQL cannot infer what type $1 is. You can see the same thing in the psql client.
jack=# prepare ps as select 'foo' where $1 is null;
ERROR: could not determine data type of parameter $1
Solution is to include a typecast.
jack=# prepare ps as select 'foo' where $1::int is null;
PREPARE
I'm trying to run a query with an optional WHERE:
Table videos looks like:
But this returns the error
could not determine data type of parameter $1 (SQLSTATE 42P08)
. The error occurs both when userid.Valid is true and when false.What am I doing wrong?
The text was updated successfully, but these errors were encountered: