PostgreSQL: Replace values greater than a certain limit in array elements

Question

I have 3 rows of array (type, text) in my PostgreSQL 9.5 database like follows:

ID     array
1      "112,292,19.3"
2      "203,383,22.1"
3      "136,226,18.9,286,346,27.1,346,406,6.5"

There are some array elements which are greater than 360. I want to replace them on a condition that if any array element > 360 then element - 360 so that the replaced array be like:

ID     array
1      "112,292,19.3"
2      "203,23,22.1"
3      "136,226,18.9,286,346,27.1,346,46,6.5"

How can I replace values greater than 360?

360 is the certain limit for the elements. That's why I want to scan all the elements if they are greater than 360. — khajlk
– khajlk, Commented Jun 11, 2017 at 13:42
try mod as in my answer example. of course it will give replace 721 with 1, not 361, but I have an impression it's what you finaly will want?.. — Vao Tsun
– Vao Tsun, Commented Jun 11, 2017 at 13:44

klin · Accepted Answer · 2017-06-11 13:46:01Z

3

There is modulo operator % in Postgres:

with my_table(id, arr) as (
values
    (1, array[112,292,19.3]),
    (2, array[203,383,22.1]),
    (3, array[136,226,18.9,286,346,27.1,346,406,6.5])
)

select id, array_agg(unnest % 360 order by ordinality)
from my_table,
unnest(arr) with ordinality
group by 1;

 id |               array_agg                
----+----------------------------------------
  1 | {112,292,19.3}
  2 | {203,23,22.1}
  3 | {136,226,18.9,286,346,27.1,346,46,6.5}
(3 rows)

answered Jun 11, 2017 at 13:46

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Abelisto Over a year ago

Probably more compact (I believe that the order is stored): select id, array(select unnest(arr) % 360) from my_table;

klin Over a year ago

This assumption is a bit risky. When in future you add something (e.g. a where condition) you'll be surprised why the order is changed.

Abelisto Over a year ago

Yes. But from other hand we do not need the additional memory for join result and additional scan for grouping. Risk vs efficiency :)

Vao Tsun · Accepted Answer · 2017-06-11 13:41:22Z

1

try https://www.postgresql.org/docs/9.5/static/functions-math.html mod

remainder of y/x

eg:

x=# with a as (select * from unnest(array[136,226,18.9,286,346,27.1,346,46,6.5]) with ordinality a(i,o))
select array_agg(mod(i,360) order by o) from a;
               array_agg
----------------------------------------
 {136,226,18.9,286,346,27.1,346,46,6.5}
(1 row)

answered Jun 11, 2017 at 13:41

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Replace values greater than a certain limit in array elements

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related