CODEWITHSUNDEEP
sqlarrayspostgresqlreplaceconditional-statements
khajlk
khajlk

Reputation: 861

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

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?

Upvotes: 0

Views: 1009

Answers (2)

klin
klin

Reputation: 121634

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)

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51519

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)

Upvotes: 1

Related Questions