1

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) || '%'));
Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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. Commented Mar 29, 2019 at 14:45
  • @exhuma ok. so make friends with unnest :-) Commented Mar 29, 2019 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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.