Início / dba / Perguntas / 117740
Accepted
Christian Schmitt
Asked: 2015-10-13 05:16:02 +0800 CST

Consulta PostgreSQL LIKE no campo ARRAY

  • 772

Existe alguma maneira de ter uma LIKEconsulta Postgres em um campo ARRAY?

Atualmente eu quero algo assim:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Atualmente mais baixo não é necessário tanto. No entanto, deve encontrar UM campo correspondente dentro do ARRAY. É mesmo possível?

Atualmente eu uso uma visão materializada para gerar a tabela "list" com um JOIN e um ARRAY_AGG(), já que eu JOIN uma tabela onde mais valores poderiam estar na tabela certa. O que duplicaria os campos da tabela à esquerda, o que não é o que eu quero.

Edite é assim que eu crio a visão (realmente lenta e feia):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id, 
       a.oa_nr, 
       a.date_deleted, 
       a.lock, 
       a.sds_nr, 
       a.kd_art_nr, 
       a.kd_art_index, 
       a.kd_art_extend, 
       a.surface, 
       a.execution, 
       a.surface_area, 
       a.cu_thickness, 
       a.endintensity, 
       a.drilling, 
       array_agg(o.id::text) AS offer_list 
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr 
GROUP BY .....;

Também preciso retornar os IDs da task_offertabela.

database-design postgresql

2 respostas

  1. Best Answer

    Você pode usar unnest()como @dezso já mencionado, por exemplo, com uma LATERALjunção (Postgres 9.3+)

    SELECT l.*
FROM   list l, unnest(array_field) a  -- implicit lateral
WHERE  lower(a) LIKE '1234%';

    Você não precisa de nada disso para o caso apresentado. Nenhuma visão materializada. Essa consulta nas tabelas subjacentes é mais rápida, pois pode usar um índice:

    SELECT *  -- or selected columns
FROM   article_list a
JOIN   LATERAL  (                      -- only matching IDs
   SELECT array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  article = a.oa_nr            -- LATERAL reference  
   AND    id::text ILIKE '1234%'       -- or just LIKE
   ) o ON offer_list IS NOT NULL;

    offer_listé uma matriz do tipo de dados original no meu resultado e contém apenas IDs correspondentes.

    Retorna uma única linha de article_listcom uma matriz de IDs correspondentes em task_offer. Adicione um text_pattern_opsíndice (para correspondências ancoradas à esquerda) para obter o melhor desempenho de leitura:

    CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) text_pattern_ops);

    Ou um índice de trigramas para correspondências infixas (não ancoradas à esquerda):

    Ou talvez uma junção simples para padrões seletivos:

    SELECT a.*, o.offer_list -- or selected columns
FROM   article_list a
JOIN   (                          -- only matching IDs
   SELECT article, array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  id::text ILIKE '1234%'  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;

    Precisa de um índice em article_list, também. (O que você provavelmente tem - nenhuma informação na pergunta.) Como:

    CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);
    • 15

  2. Você já considerou a extensão parray_gin , também localizada aqui ?

    Ainda não o usei em produção, mas pelo que vi ele faz o que você deseja contra o array. Você pode criar o índice relevante na visão materializada.

    No entanto, eu só faria isso se você tivesse a visão materializada de qualquer maneira. Caso contrário, acho que a resposta do @ErwinBrandstetter é melhor do que criar uma visão materializada apenas para conter o índice.

    • 4

relate perguntas