CODEWITHSUNDEEP
arrayspostgresqlliquibase
Thomas Stubbe
Thomas Stubbe

Reputation: 2014

Postgres: modify each array element

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)

Upvotes: 3

Views: 2713

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 12

Related Questions