Início / dba / Perguntas / 198145
Accepted
beldaz
Asked: 2018-02-17 13:56:43 +0800 CST

Remodelar array no PostgreSQL

  • 772

Estou usando um servidor PostgreSQL 10, no qual tenho algumas tabelas contendo atributos do tipo double precision[]destinados a armazenar arrays 1D de dados, de comprimento ~1000. Meu código interagindo com o banco de dados continha um bug que inseria dados como um array de arrays de comprimento 1 (veja meu relatório de bug psycopg onde acabei descobrindo que era isso que estava acontecendo), mas isso foi permitido pelo DBMS :

A implementação atual também não impõe o número declarado de dimensões. As matrizes de um tipo de elemento específico são todas consideradas do mesmo tipo, independentemente do tamanho ou do número de dimensões. Portanto, declarar o tamanho do array ou o número de dimensões em CREATE TABLEé simplesmente documentação; ele não afeta o comportamento em tempo de execução.

Como posso corrigir esses arrays deformados dentro do DBMS (ou seja, usando SQL em vez de atualizar a partir de código externo)? Basicamente, desejo apenas achatar cada matriz em uma matriz 1D, conforme pretendido originalmente.

Um trecho dos dados da matriz conforme retornado por 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 respostas

  1. Best Answer

    Algo assim deve funcionar:

    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;

    Exemplo de saída do 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

relate perguntas