Postgres:修改每个数组元素 [英] Postgres: modify each array element

问题描述

我有一个postgres列double []:{100，101，102}.

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

我想将每个元素除以10，因此结果应为{10.0，10.1，10.2}.

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

我只找到了带有for语句的解决方案，但是如何通过一个简单的查询来实现呢? (我需要通过liquibase更新)

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

替代方法是编写Java迁移，但我希望使用简单的查询...

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

提前谢谢！

更新:

出现的第二个问题是:

通过Liquibase java-migration脚本执行此操作时，您会得到一个liquibase.database.jvm.JdbcConnection(通过liquibase.change.custom.CustomTaskChange)，它当然不支持postgres-arrays =/.

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 =/.

如何以这种方式处理数组? (我使用liquibase-core 3.5.5)

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

推荐答案

您需要取消嵌套，划分然后聚合回去.

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));

如果需要保留数组中的原始顺序，请使用with ordinality

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);

---

要在Liquibase中运行此程序，您需要使用<sql>更改

在线示例: https://rextester.com/IJGA96691

这篇关于Postgres:修改每个数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！