主页 / dba / 问题 / 198145
Accepted
beldaz
Asked: 2018-02-17 13:56:43 +0800 CST

在 PostgreSQL 中重塑数组

  • 772

我正在使用 PostgreSQL 10 服务器，其中有一些表包含double precision[]用于存储一维数据数组的属性类型，长度约为 1000。我与数据库交互的代码包含一个错误，该错误将数据作为长度为 1 的数组插入（请参阅我的 psycopg 错误报告，我最终发现这是正在发生的事情），但DBMS 允许这样做：

当前的实现也不强制执行声明的维数。特定元素类型的数组都被认为是同一类型，无论大小或维数如何。因此，在中声明数组大小或维数 CREATE TABLE只是文档；它不会影响运行时行为。

我如何修复 DBMS 中的这些畸形数组（即，使用 SQL 而不是从外部代码更新）？我基本上希望按照最初的预期将每个数组展平为一维数组。

返回的数组数据片段psql是：

{{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.
20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081},{2.2031
618420891},{2.20265334228114},{2.20212174686242},{2.20156895755458},{2.201000532
12516},{2.20042484573123},{2.19985210697606},{2.19929335742568},{2.1987595765431
},{2.19826098783408},{2.19780662274576},{2.19740415504621},{2.19705997866427},{2
.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181},{2.19638 ...
postgresql array

1 个回答

  1. Best Answer

    这样的事情应该有效：

    update the_table 
  set data = x.data
from (
  select t.id, array_agg(d.x order by d.idx) as data
  from the_table t, unnest(data) with ordinality as d(x, idx)
  group by t.id
) x
where x.id = the_table.id;

    示例 psql 输出：

    postgres=> create table the_table (id integer primary key, data double precision[]);
CREATE TABLE
postgres=> insert into the_table values
postgres->   (1, '{{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081}}'),
postgres->   (2, '{{2.19780662274576},{2.19740415504621},{2.19705997866427},{2.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181}}');
INSERT 0 2
postgres=> select *
postgres-> from the_table;
 id |                                                                           data
----+----------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081}}
  2 | {{2.19780662274576},{2.19740415504621},{2.19705997866427},{2.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181}}
(2 rows)

postgres=> update the_table
postgres->   set data = x.data
postgres-> from (
postgres(>   select t.id, array_agg(d.x order by d.idx) as data
postgres(>   from the_table t, unnest(data) with ordinality as d(x, idx)
postgres(>   group by t.id
postgres(> ) x
postgres-> where x.id = the_table.id;
UPDATE 2
postgres=> select *
postgres-> from the_table;
 id |                                                                   data
----+------------------------------------------------------------------------------------------------------------------------------------------
  1 | {2.20751909662576,2.20679071024511,2.20615506273571,2.2055910715332,2.20507756148068,2.20459435596551,2.20412336646322,2.20364958013081}
  2 | {2.19780662274576,2.19740415504621,2.19705997866427,2.19677947299938,2.1965673849541,2.19642825612809,2.1963668334181}
(2 rows)

postgres=>
    • 1

相关问题