Parameter type inference not working for VALUES tables?

[darkgnotic]

For a select query with a parameter, an integer value is correctly identified as an INT4 (type = 23):

await sql`CREATE TABLE foo (id int)`;

console.log(
  await sql`SELECT * from foo WHERE id = ${1}`.describe(),
);

//      {
//        string: 'SELECT * from foo WHERE id = $1',
//        types: [ 23 ],
//        name: '9njwfoixz23',
//        columns: [
//          {
//            name: 'id',
//            parser: [Function: parse],
//            table: 462973,
//            number: 1,
//            type: 23
//         }
//        ]
//      }

However, for a query over a VALUES table, the same parameterized value is identified as TEXT (type = 25):

console.log(
  await sql`WITH bar (id) AS (VALUES (${1})) SELECT * FROM bar`.describe(),
);

//      {
//        string: 'WITH bar (id) AS (VALUES ($1)) SELECT * FROM bar',
//        types: [ 25 ],
//        name: 'owdbjhlx58l2',
//        columns: [ { name: 'id', parser: undefined, table: 0, number: 0, type: 25 } ]
//      }

And the result is similarly incorrect ({ id: '1' }).

Is this expected, and if so, is there a way to infer the value type from the JavaScript type?

[porsager]

Yes, that's expected, and v4 will most likely drop any kind of lazy js inference completely as it is was a bad idea in the first place.

Check out https://github.com/porsager/postgres/pull/392

For your example you'd solve it like this:

WITH bar (id) AS (VALUES (${1}::int)) SELECT * FROM bar

[darkgnotic]

Thank you for the quick response and workaround! 🙏