Postgres ltree multiple paths

2
votes

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.*

2
postgresqlltree
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

2 Answers

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

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).*'