So I have a Node.js query that should pass in these three values and update a specific column by a specific amount for a user with a specific id:

await client.query(sqlStatement, [columnName, changeBy, id])

The 'sqlStatement' looks like:

'UPDATE tableName SET ($1) = ($1)   ($2) WHERE id = ($3)'

As you can see, it should pass in the values such that the SQL statement looks like:

'UPDATE tableName SET columnName = columnName   changeBy WHERE id = id'

I've also tried to write sqlStatement as the below (and just pass in columnName twice into the query but this also results in an error:

'UPDATE tableName SET ($1) = ($2)   ($3) WHERE id = ($4)'

The error returned is error: syntax error at or near "$1".

Not sure how to fix this--any help is much appreciated.

CodePudding user response：

The reason this is happening is that node-postgres doesn't support query parameters for identifiers and your column name after SET is an identifier. Also, even if this worked, unless node-postgres somehow substituted the entire ($1) (with parentheses) for your value, you'd get

ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression.

If you wish to keep node-postgres, its documentation recommends to use pg-format in such cases, to build a dynamic SQL statement. If you consider alternatives, you can look into Knex.js that will build the queries for you, like so:

knex('tableName')
   .update({ 
       columnName: knex.raw('??   ?',['columnName',changeBy])
   })
   .where('id', id)

And in the meanwhile, as a workaround, you should be able to set your sqlStatement to a dynamic SQL query on your own:

do language plpgsql $$ begin execute format('UPDATE test SET %I = %I   %s WHERE id = %s','$1','$1',$2,$3); end$$;

and try with that. Note that I removed the parentheses from around the update column to avoid the multiple-column UPDATE error. What should happen is that node-postgres evaluates the above to

do language plpgsql $$ begin execute format('UPDATE test SET %I = %I   %s WHERE id = %s','col','col',51,72); end$$;

and pass it to PostgreSQL, which should execute it as

UPDATE test SET "col" = "col"   51 WHERE id = 72;

From the doc:

• %I is for Identifiers, that PostgreSQL will automatically quote if needed.
• %L is for string Literals
• %s is for simple string substitution, in the sense that it will not be quoted, so it can be used for numeric literals