3

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.

Haidar Aji
  • 33
  • 4

2 Answers2

2

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
klin
  • 112,967
  • 15
  • 204
  • 232
  • There is a problem with those two though, when there's a '003.001' path, the '003.001' will still be included in the query result – Haidar Aji Jun 20 '17 at 01:36
2

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132