Search with multiple parameter in postgresql ltree

3
votes

I planned to implement a database that using ltree as multiple level categorization. However i ran into trouble when i tried to get an entry with path x or y.

         new_table
+-------+--------+---------+
|  id   |  name  |   path  |
----------------------------
|   1   |    a   |   001   |
|   2   |    b   |   002   |
|   3   |    c   | 001.001 |
|   4   |    d   | 002.001 |
|   5   |    e   |   003   |
----------------------------

With the table stated below, i want to get an id which started with either 001 or 002. However i can't seems to get the right query for it.

expected result: 1,2,3,4
this works: select id from new_table where path <@ '001' or path <@ '002'
this doesn't (results in a syntax error): select id from ingredient where ingredient_path <@ '001|002'

This left me confused as the documentation stated that using | (or) symbol is acceptable.

I'm very new to ltree and hoping that i can get an answer that quite easy to understand.

2
postgresqlltree

2 Answers

2
votes

The symbol | (or) is acceptable in ltxtquery so use the ltree @ ltxtquery operator:

select id from new_table where path @ '001|002'; -- find labels 001 or 002
-- or
select id from new_table where path @ '001*|002*'; -- find labels starting with 001 or 002
1
votes

try:

select id from new_table where path ~ '001|002.*'

OR won't work for <@ operator I think, as per docs,

ltree <@ ltree

and | can be used in lquery instead