CODEWITHSUNDEEP
postgresql
EMF
EMF

Reputation: 165

Looping Inside a Postgres UPDATE Query - Part 2

NOTE: This is very similar to, but not the same as my other recent question at Looping inside a Postgres UPDATE query. The answer provided on that question works perfectly, but the client wants each value calculated using a specific formula rather than a randomized selection to enforce an even distribution.

(Postgres 10.10) I have the following fields in my_table:

loval INTEGER 
hival INTEGER 
valcount INTEGER 
vals INTEGER[]

I need to set vals to an array containing valcount - 2 integers (loval and hival are included in the final array) each calculated as follows using n as a loop counter running from 2 to valcount-1: 

loval + (n - 1) * ((hival - loval) / (valcount- 1))

So for:

loval: 3 
hival: 22 
valcount: 6

I'm looking to set vals to:

{3, 7, 11, 14, 18, 22}

I know how to do this with an inefficient "loop through the cursor" solution, but I'm wondering if Postgres has a way to do a looping computation inline or a built-in distribution function that can do this.

Upvotes: 1

Views: 121

Answers (3)

David
David

Reputation: 377

You can edit the answer from the post you linked in this way:

update my_table
  set vals = array_append(array_prepend(loval,array(select (loval + (n-1) * ((hival - loval)::decimal / (valcount- 1)))::int 
             from generate_series(2, valcount-1) as n)),hival);

Demo:

| loval | hival | valcount | vals            |
| ----- | ----- | -------- | --------------- |
| 3     | 22    | 6        | 3,7,11,14,18,22 |

::decimal will make sure that the division is done with floating numbers so that your second value is 7 instead of 6 with only integers. array_prepend and array_append will add an element at the beginning and at the end of the array

Upvotes: 1

sticky bit
sticky bit

Reputation: 37487

Use generate_series() to create the running n for your formula. Use the WITH ORDINALITY option of generate_series() to have an ordinal number you can order the results by. Make sure you cast (at least) one operand of the division in your formula to decimal as otherwise integer division is used. Use round() to round the result. Then use array_agg() to build the array. Use ORDER BY for array_agg() to order the array elements by the ordinal number generate by generate_series(). To update each row you can use a subquery.

UPDATE my_table t
       SET vals = (SELECT array_agg(round(t.loval + (gs.n - 1) * ((t.hival - t.loval)::numeric / (t.valcount - 1))) ORDER BY gs.o)
                          FROM generate_series(1, t.valcount) WITH ORDINALITY gs (n, o));

db<>fiddle

Upvotes: 1

GMB
GMB

Reputation: 222622

You could make use of generate_series() and array_agg() like so:

select 
    t.*,
    (
         select array_agg(loval + (n - 1) * (hival - loval) / (valcount - 1) order by n)
         from generate_series(1, valcount) as d(n)
    ) vals
from t

Demo on DB Fiddle:

| loval | hival | valcount | vals            |
| ----- | ----- | -------- | --------------- |
| 3     | 22    | 6        | 3,6,10,14,18,22 |

Upvotes: 0

Related Questions