PostgreSql: how to update table from array?

Question

How to update table fields from array to avoid temp table using? User passes array to reorder table.

create TABLE items
(
id serial primary key,
name text, 
select_order int
)

insert into items(name, select_order)
values
('cucumber',0),
('milk',1),
('coffee',2),
('water',3)

select * from items order by select_order;

DO
$body$
DECLARE var_array int[]:='{3,0,2,1}';
BEGIN
update items ??

END;
$body$

The final result in this example should be

select * from items order by select_order;
name        select_order
------------------------
water       0
cucumber    1
coffee      2
milk        3

Can be a little more specific? How do want this update to happen? I cannot figure it out from your question, var_array is just a list of integers. — redneb
– redneb, Commented Sep 6, 2016 at 7:43
Index in the input array is a corresponding old position. As I see from answers below th "ordinality" is a right way — ZedZip
– ZedZip, Commented Sep 6, 2016 at 9:26

user330315 · Accepted Answer · 2016-09-06 07:55:55Z

6

Assuming the index in the array corresponds to the value in select_order the following query returns the new and old value:

select i.id, i.select_order, t.new_sort
from items i
   join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order

Now this can be used to update the target table:

update items
  set select_order = r.new_sort
from (
  select i.id, i.select_order, t.new_sort
  from items i
    join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order
) r
where r.id = items.id;

This also assumes that select_order starts a 0 and has no gaps.

answered Sep 6, 2016 at 7:55

user330315

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

Collectives™ on Stack Overflow

PostgreSql: how to update table from array?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related