Postgres: modify each array element

Question

I have a postgres column double[]: {100, 101, 102}.

I want to divide each element by 10, so the result should be {10.0, 10.1, 10.2}.

I've only found solutions with for-statements, but how to realize this with a simple query? (I need to update through liquibase)

The alternative is to write a Java-migration, but I would prefer a simple query...

Thanks in advance!

UPDATE:

A second problem that arose is:

When doing this through a Liquibase java-migration script, you get a liquibase.database.jvm.JdbcConnection (through liquibase.change.custom.CustomTaskChange), which of course does not support postgres-arrays =/.

How to handle arrays this way? (I use liquibase-core 3.5.5)

I know right :) The downside on the other side would be a lot of extra tables, references, etc... since we got about 8 properties as array, each with about 3000 values — Thomas Stubbe
– Thomas Stubbe, Commented Mar 5, 2019 at 17:17

user330315 · Accepted Answer · 2019-03-05 17:44:21Z

12

You need to unnest, divide, then aggregate back.

update the_table
  set the_array = array(select t.val / 10 
                        from unnest(the_table.the_array) as t(val));

If you need to preserve the original order in the array use with ordinality

update the_table
  set the_array = array(select t.val / 10 
                        from unnest(the_table.the_array) with ordinality as t(val,idx) 
                        order by t.idx);

To run this in Liquibase you need to use a <sql> change

Online example: https://rextester.com/IJGA96691

answered Mar 5, 2019 at 17:44

user330315

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

1 Comment

Thomas Stubbe Over a year ago

You're my hero! Thx!

Collectives™ on Stack Overflow

Postgres: modify each array element

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related