0

How to check if a node is repeated (exits more than one) in a ltree?

Something like this:

'0.1.2.3.4.3.9' ==> True
'0.1.2.3.4.5.9' ==> False
Milad
  • 692
  • 2
  • 9
  • 27

1 Answers1

1

Convert it to a string, split that into a table and check if there are duplicate entries:

SELECT EXISTS (SELECT 1
               FROM regexp_split_to_table(
                       ltree2text('0.1.2.3.4.3.9'),
                       '\.'
                    ) AS labels(label)
               GROUP BY label
               HAVING count(*) > 1
              );

A better option may be a smart regular expression, based on your comment:

SELECT ltree2text('0.1.2.3.4.5.9') ~ '(\m\d*\M).*\1';
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I couldn't find a way to try this on the ltree column of my table. But this looks slow. Is there a faster way to do it? – Milad Jun 09 '21 at 07:24
  • Perhaps there is a faster way, but I cannot think of one. It is an unusual requirement. – Laurenz Albe Jun 09 '21 at 07:27
  • I found a way that could detect some duplicated noted (but not all). But can't implement it. It's like this: `where path ~ '(\.\d+\.).*\1'` But it says: `ERROR: lquery syntax error at character 1` Do you know what the problem is? – Milad Jun 09 '21 at 07:30
  • Ah, good idea. I have added a fixed version to my answer. – Laurenz Albe Jun 09 '21 at 07:35
  • Do I have to convert ltree to text? It might make it slow and I read in the official doc that it can be used like this: `ltree ~ lquery → boolean`. But didn't work for me. It's weird. The regex is more weird because it's not like normal regex. I got syntax error for my pattern and yours has `\m` and `\M` which I don't know what they mean. Is there a doc for this type of regex that I can read? – Milad Jun 09 '21 at 07:43
  • 1
    For an `ltree`, the operator `~` is *not* the regular expression matching operator, so you have to convert to text. Regular expressions are documented [here](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP). I wouldn't worry about the cost of converting to `text`, the regular expression match will be *much* more expensive. – Laurenz Albe Jun 09 '21 at 07:46