could not determine data type of parameter $1

[1zzang-sm]

• CASE A SELECT ST_DistanceSphere( ST_GeomFromText('POINT(127.058923 37.242621)', 4326), "geometryType")as distance FROM "MyTable" WHERE ST_DistanceSphere( ST_GeomFromText('POINT(127.058923 37.242621)', 4326), "geometryType") < 500;
• CASE B SELECT ST_DistanceSphere( ST_GeomFromText('POINT(${longitude} ${latitude})', 4326), "geometryType")as distance FROM "MyTable" WHERE ST_DistanceSphere( ST_GeomFromText('POINT(${longitude} ${latitude})', 4326), "geometryType") < 500;

PostGIS is properly installed in my database. When performing as in A, the query succeeds normally, but when performing as in B, an error of "could not determine data type of parameter $1" occurs.

[adamgreg]

I think I might be hitting the same problem when using prepared statements:

await sql`PREPARE prep(integer) AS SELECT $1`;

// This works
await sql`EXECUTE prep(0)`;

// This fails with PostgresError: could not determine data type of parameter $1
await sql`EXECUTE prep(${0})`;

// This also fails the same way
await sql`EXECUTE prep(${0}::integer)`;

[brianlaw033]

Have you fixed it? I am having the same error with postgis

[1zzang-sm]

SELECT * ST_DistanceSphere( ST_SetSRID(ST_MakePoint(${lng}, ${lat}), 4326), coords ) as distance FROM "MyTable" WHERE ST_DistanceSphere( ST_SetSRID(ST_MakePoint(${lng}, ${lat}), 4326), coords ) < ${radius} I solved it this way Would you like to give it a try?

[brianlaw033]

Turns out I have to use sql.unsafe Here is my code sql`alter table draft.${sql(draftName)} alter column geom type geometry(${sql.unsafe( geometryType, )}, 4326) using ST_Transform(ST_SetSRID(geom, ${sql.unsafe(srid)}),4326);`

[humphd]

I hit this today as well, and in my case, had to also convert to a string:

const maxWords = 3000;
//...
sql`SELECT ... ts_headline(..., MaxWords=${sql.unsafe(maxWords.toString())}, ...`

Not fun or obvious.

[porsager]

@humphd and also a very wrong way to do it 🤨

This works just fine

const maxWords = 3000;
//...
sql`SELECT ... ts_headline(..., ${ 'MaxWords=' + maxWords })`

[humphd]

@humphd and probably not the best way to do it either 🤨

Try and post a repro and your actual query

    const searchTerms = 'a:* | b:*';
    const maxWords = 3000;
    const sql = createSql();

    const results = await sql`
       WITH query_terms AS (
            SELECT to_tsquery(${searchTerms}) AS query
        ),
        texts AS (
            SELECT f.id AS file_id,
                COALESCE(f.text, '') AS text
            FROM file_v2 f
        ),
        search_results AS (
            SELECT ft.file_id,
                ft.text,
                ts_headline(
                    'english', ft.text,
                    (SELECT query FROM query_terms),
                    'MaxWords=${maxWords}, MinWords=10'
                ) AS context
            FROM texts ft
            WHERE to_tsvector('english', ft.text) @@ (SELECT query FROM query_terms)
        )
        SELECT file_id, context
        FROM search_results;
        `

Fails:

node:internal/process/promises:289
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: could not determine data type of parameter $2
    at ErrorResponse (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:376:12)
    at readableAddChunk (node:internal/streams/readable:349:9)
    at Readable.push (node:internal/streams/readable:286:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at cachedError (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/query.js:170:23)
    at new Query (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/query.js:36:24)
    at sql (file:///workspaces/DeepStructure/node_modules/.pnpm/[email protected]/node_modules/postgres/src/index.js:112:11)
    at main (/workspaces/DeepStructure/sdk/npm/src/components/assistants/workflows/context-strategies/test.tsx:8:27) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P18',
  file: 'postgres.c',
  line: '707',
  routine: 'pg_analyze_and_rewrite_varparams'
}

[porsager]

Did a quick test and edited my comment above the same time you posted..

[porsager]

If you read the docs you'll see that parameters have to be valid PostgreSQL protocol level parameters. It is eg. the same reason you have to do

select * from x where x like ${ '%' + something }

and not

select * from x where x like '%' ${ something }

There's nothing in this library that magically handles unknown assumptions about text concatenation.

Now you could do

select * from x where x like '%' || ${ something }

But then you've also writing valid sql.

[humphd]

OK, this wasn't obvious to me (I read the docs). Thanks for clarifying my case.

[porsager]

You're welcome ☺️ Hope it's obvious in hindsight 😉

It's the same issue in the original post.. @1zzang-sm

Either do like this

const point = `POINT(${longitude} ${latitude}`

sql`
  SELECT ST_DistanceSphere(
  ST_GeomFromText(${ point }, 4326), "geometryType") as distance FROM "MyTable"
  WHERE ST_DistanceSphere(
  ST_GeomFromText(${ point }, 4326), "geometryType") < 500;
`

or like this

sql`
  SELECT ST_DistanceSphere(
  ST_SetSRID(ST_MakePoint(${ long }, ${ lat }), 4326), "geometryType") as distance FROM "MyTable"
  WHERE ST_DistanceSphere(
  ST_SetSRID(ST_MakePoint(${ long }, ${ lat }), 4326), "geometryType") < 500;
`

[porsager]

I think I might be hitting the same problem when using prepared statements:

await sql`PREPARE prep(integer) AS SELECT $1`;

// This works
await sql`EXECUTE prep(0)`;

// This fails with PostgresError: could not determine data type of parameter $1
await sql`EXECUTE prep(${0})`;

// This also fails the same way
await sql`EXECUTE prep(${0}::integer)`;

@adamgreg This is not the same. You can't use protocol level parameters when calling prepared statements like that.

Even so.. Postgres.js automatically makes prepared statements for you, so the reason for doing what you're trying might be completely unnecessary or you'll need to provide more info.