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.
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