parameterization is not working correctly for FROM queries

[nhumrich]

If I use the FROM syntax during an UPDATE statement, it seems that the parameterize of this library always assumes "text" event when it shouldn't be. I have written a fully working example here: https://gist.github.com/nhumrich/d330d167b4650f32cc33cfcb992123c0

If you run a basic query where bar is a DOUBLE PRECISION column, everything works. But if you use this syntax instead, it says its text for some reason:

'UPDATE foo d SET bar=v.bar FROM (values ($1, $2)) as v(id,bar) WHERE d.id=v.id', ['abc', 4]

You get the following error form postgres:

error: column "bar" is of type double precision but expression is of type text

If I dont use parameters, and instead use the exact values in the query, it works perfectly, as expected. You can see this example in the gist. Obviously, "not using parameters" is not a workaround, because of sql injection.

[brianc]

try:

UPDATE foo d SET bar=v.bar FROM (values ($1, $2::double)) as v(id,bar) WHERE d.id=v.id', ['abc', 4]

[nhumrich]

if I use $2::double precision it works. I am not really happy with that workaround though, as the section of code I am using it is pretty generic. But I can deal with it.
Is this something that can potentially be fixed in this library? Or do I have no option but to cast every time?

[brianc]

I haven't seen this kind of thing come up much tbh. There is a way to specify exact type OIDs to the query as type hints if you need to. If you want to submit a PR for a fix I'll happily review it!

[nhumrich]

I am totally out of my element. Could you point me to a file/class where this change would need to happen? Or at least where i sound start looking?

[charmander]

this library always assumes "text"

PostgreSQL is what assumes text when nothing is giving it better information.

PREPARE x AS (SELECT a + 1 FROM (VALUES ($1)) AS t (a));
-- ERROR:  operator does not exist: text + integer

In these situations, it is normal to give a type hint like ::double, although it’s true that pg does have the ability to provide oids outside the query with types:

node-postgres/packages/pg/lib/query.js

Line 24 in 41c899c

this.types = config.types

(it’s very hard to use right now because it also gets passed to Result, which expects to be able to use it to select parsers, but it’s there)

pg can’t create this automatically from an array of values with the existing API, if that’s what you were hoping for. The JavaScript types that are allowed right now each correspond to too many PostgreSQL types.

[nhumrich]

PostgreSQL is what assumes text when nothing is giving it better information.

Thats fair. I did try other libraries in other languages though, like sqlalchemy (python, which has the same mapping problem as javascript), and none of them have this problem. I will look into this though.

[stagefright5]

This is the query I am running, and I am facing this same issue:

UPDATE afm as fm
    SET
        fl = c.fl,
        so = c.so
FROM (VALUES
    ($1 , $2 , $3)  ,($4 , $5 , $6 )  ,($7 , $8 , $9 ) 
) as c(afid, fl, so) 
where c.afid = fm.afid AND fm.tid = $10

with the following parameters array

[36, 'F', 1, 35, 'U', 2, 29, 'C', 3, 5]

As it can be seen, every n%3 !== 2 element is type of integer. So, the working query for me, as suggested above, is

UPDATE afm as fm
    SET
        fl = c.fl,
        so = c.so
FROM (VALUES
    ($1::integer , $2 , $3::integer )  ,($4::integer , $5 , $6::integer )  ,($7::integer , $8 , $9::integer ) 
) as c(afid, fl, so) 
where c.afid = fm.afid AND fm.tid = $10

[chrisdavies]

I'm getting around this by passing my values through a function that looks something like this:

function refNameWithType(name: string, val: any) {
  switch (typeof val) {
    case 'number':
      return `${name}::double precision`;
    case 'string':
      return `${name}::text`;
    case 'boolean':
      return `${name}::boolean`;
    default:
      // Not sure about this one ... we may need to handle dates specially.
      return `${name}::text`;
  }
}

Anyway, I'm only using this logic when doing a bulk update / update from, which is the only place it seems to show up. The logic is still pretty general. I think it's probably right to not have this library magically handle this case, since it's a postgres behavior. I like the fact that this lib is a thin library on top of postgres.

[shaneosullivan]

I see the same issue but for UPDATE queries that use the CASE/WHEN syntax like

UPDATE test_repro
    SET
      num = (
        CASE
          WHEN id = $1 then $2
        END
      )
    WHERE id in ($3)
;

See the full script at
https://gist.github.com/shaneosullivan/555419454e536823d6f56fbc04612096