Dynamic Case statement in postgres

Question

I am having two arrays. Both array calculated from functions so both arrays are dynamic but length of both arrays will be same.

a1= ARRAY[1,2,3];
a2= ARRAY[10,20,30];

Now I want to update my table something like this

UPDATE TABLE SET data= CASE 
     data=a1[1] then a2[1]
     data=a1[2] then a2[2]
     data=a1[3] then a2[3]END
where id=1;

I tried with adding loop inside CASE but it is not working .

Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented May 30, 2018 at 12:30

JGH · Accepted Answer · 2018-05-30 12:40:00Z

1

You can make use of array_position to find the matching index in array 1, and query array 2 using this index:

UPDATE TABLE 
SET data = a2[array_position(a1, data)]
WHERE id = 1;

answered May 30, 2018 at 12:33

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

1 Answer 1