Postgres ltree multiple paths

Question

Okay, so I have a table with ltree on a column called path. I want to select multiple paths, but I don't want to have a ton of OR statements. Is that possible or is this the best way to do it?

Paths:

'schools.myschool.*'
'companies.about.*'
'testing.information.content.*'

Query:

SELECT content, path FROM threads WHERE path ~ 'schools.myschool.*' OR path ~ 'companies.about.*' OR path ~ 'testing.information.content.*

If you want `.` as separator, you should use `\.` instead of `.`, and if the suffix should be separated by a dot too, you should express that with `\..*` or `\..+`. — clemens, Sep 29 '17 at 06:32

score 2 · Answer 1 · answered Mar 11 '14 at 08:21

2

select 'schools.myschool.*' ~ any(array[
    'schools.myschool.*', 'companies.about.*', 'testing.information.content.*'
]);
 ?column? 
----------
 t

answered Mar 11 '14 at 08:21

Clodoaldo Neto

118,695
26
233
260

I like this solution, but just for clarification, I think the OP means `.*` as a wildcard. Your expression should match `schools.myschool.yourclass` for instance. ;) – clemens Sep 29 '17 at 06:17

score 1 · Answer 2 · answered Sep 29 '17 at 06:10

1

You can join the regular expressions into one with the or-operator |, and by separating the common suffix:

SELECT content, path FROM threads 
WHERE path ~ '(schools.myschool|companies.about|testing.information.content).*'

answered Sep 29 '17 at 06:10

clemens

16,716
11
50
65

Postgres ltree multiple paths

2 Answers2