7

In regards to UPDATE multiple rows from multiple params in nodejs/pg, I need to run the following:

update portfolios p set votes = s.votes from unnest(array[(5, 1), (15, 1), (25, 2)]) s (votes int, id int) where p.id = s.id

where my array in unnest is $1, as follows:

update portfolios p set votes = s.votes from unnest($1) s (votes int, id int) where p.id = s.id

However, my array originally consist of objects, as:

[{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]

I've tried to convert it with:

my_array = my_array.map(function(e) { return tuple(e.votes, e.id); });

But that fails.

I need to correct compatible array with values for use with pg and Client.query.

How can I convert my array of objects to respect javascript and postgresql unnest?

Improve this question
7
  • Why are you needing to make this single update statement happen; why not run multiple update statements? Commented May 5, 2016 at 20:02
  • I'm using nodejs with pg which doesn't support multiple queries without making a manual loop between all statements - apparently. If other solution is available I'm all ears. Commented May 5, 2016 at 20:18
  • How does it fail? Is there a way to output the query that is being fed to Postgresql? Commented May 6, 2016 at 0:55
  • If it is not possible to get the output from the driver it is possible to see it in the Postgresql log. Commented May 6, 2016 at 0:56
  • I'm getting error 42804 "enforce_generic_type_consistency" from PostgreSQL if I keep my [{...}, {...}...] format. Commented May 6, 2016 at 5:27

2 Answers 2

Reset to default
1

You could send your JSON string as is, and have PostgreSQL deal with it:

update portfolios p set votes = s.votes from ( select (e->>'votes')::int as votes, (e->>'id')::int as id from (select (regexp_replace($1, '"\1"', 'g'))::jsonb as jarr) j cross join jsonb_array_elements(jarr) e ) s where p.id = s.id;

Where $1 is [{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]', '([a-z]+) as a string.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

@Ziggy idea to pass JSON can work although the ideal would be to make the driver adapt your array. This is the final query the driver must pass to Postgresql

update portfolios p set votes = s.votes from ( select (a->>'votes')::int as votes, (a->>'id')::int as id from ( select jsonb_array_elements(a) as a from (values ('[{"votes": 5, "id": 1}, {"votes": 15, "id": 1}]'::jsonb)) s(a) ) s ) s where p.id = s.id

And the query to pass to the driver:

update portfolios p set votes = s.votes from ( select (a->>'votes')::int as votes, (a->>'id')::int as id from ( select jsonb_array_elements(a) as a from (values (($1)::jsonb)) s(a) ) s ) s where p.id = s.id

The $1 parameter must be jsonified with something like JSON.stringify:

var a = JSON.stringify(my_array_of_objects);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.