Explicitly defining data type, but get: "could not determine data type of parameter $1"

[AmirSolt]

Version

1.25.0

What happened?

Hi, thanks for contributing to such an awsome library. I'm trying to define explicit data types in my query, but I keep getting "could not determine data type of parameter $1" error. My code:

-- name: ScanReports :many
SELECT *
FROM reports
WHERE 
ST_DWithin(
    point,
    ST_Point(@lat::DOUBLE PRECISION, @long::DOUBLE PRECISION, 3857),
    @radius::DOUBLE PRECISION
)
AND region = $4
AND occur_at >= $5
AND occur_at <= $6
ORDER BY occur_at
LIMIT $7;

instead of DOUBLE PRECISION, I've also tried: float, text. Nothing works. I'm not sure how this feature is supposed to work since documentation doesn't elaborate, so I might be completely using it wrong. Thank you.

Note: I'm using sql_package: "pgx/v5", which I can't add to playground.

Relevant log output

could not determine data type of parameter $1

Database schema

CREATE TABLE reports (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    occur_at TIMESTAMPTZ NOT NULL,
    external_src_id TEXT NOT NULL,
    neighborhood TEXT,
    location_type TEXT,
    crime_type crime_type NOT NULL,
    region region NOT NULL,
    point geometry(Point, 3857) NOT NULL,
    lat DOUBLE PRECISION NOT NULL,
    long DOUBLE PRECISION NOT NULL
);

SQL queries

-- name: ScanReports :many
SELECT *
FROM reports
WHERE 
ST_DWithin(
    point,
    ST_Point(@lat::DOUBLE PRECISION, @long::DOUBLE PRECISION, 3857),
    @radius::DOUBLE PRECISION
)
AND region = $4
AND occur_at >= $5
AND occur_at <= $6
ORDER BY occur_at
LIMIT $7;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/d0ce752bc2b6c9fb35b1b5c114ab6e8959c10b5685381cb181033ccc0b6c79c0

What operating system are you using?

Windows

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[phooijenga]

I ran into the same issue yesterday and finally figured out that this is caused by skipping a numbered parameter. sqlc can not determine the type of $1, because it is not used in your query.

If you rewrite your query to use $1..$4 instead of $4..$7, it works.

https://play.sqlc.dev/p/b601faca75148b3a49705675aedac91758588632b27de60ec4a51679057f91d6

[TobiahRex]

@AmirSolt had the same issue, but followed @phooijenga and it works!! +💯