1

This question already has an answer here:

Consider the following example:

CREATE TEMPORARY TABLE testing (
    names TEXT[]
);

INSERT INTO testing VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}')
;

SELECT * FROM testing WHERE 'toplevel1.%' ILIKE ANY(names);

I want to write a select where "any" element of an array matches a pattern. But I can't figure out a way to do it. In the example I've given, using the ILIKE operator can't work because it checks if the RHS matches the LHS. For this case I would need a similar operator but which checks if the LHS matches the RHS.

update: I'm currently fiddling around with something like this without much success:

SELECT * FROM testing WHERE names ILIKE ANY (ARRAY(SELECT unnest(names) || '%'));

marked as duplicate by jjanes, Vérace, marcello miorelli, mustaccio, dezso postgresql Apr 4 at 14:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

You can use the official ltree extension.

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TEMP TABLE testing (
    names ltree[]
);

INSERT INTO testing (names)
VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}');

SELECT * FROM testing WHERE names ~ 'toplevel1.*';

It is also doable in "plain" postgresql:

SELECT * FROM testing AS t
WHERE (
    SELECT true 
    FROM unnest(t.names) AS n
    WHERE n ILIKE 'toplevel1.%'
    LIMIT 1
);

The ltree extension is not very widely used, and obviously it has some limitations (including label length & character set), but it's stable and can achieve better performance than unnest-based version. I would really give it a try.

  • For the example I have given, this is indeed a good solution. Unfortunately I am working on a DB where I would like to avoid adding an extension as it's from a 3rd party and not 100% in our control. I'm currently only adding views. – exhuma Mar 29 at 14:45
  • @exhuma ok. so make friends with unnest :-) – filiprem Mar 29 at 14:53
0

I found a fairly similar solution with pure SQL:

SELECT * FROM testing WHERE true = ANY (ARRAY(SELECT unnest(names) ILIKE 'toplevel1%'));

This applies the ILIKE operator first over all array elements, yielding an array with only true/false values. This is then trivial to compare using the ANY operator.

Not the answer you're looking for? Browse other questions tagged or ask your own question.