sqlpostgresqlltree

How to check if a node is repeated in a ltree - PostgreSQL

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
Solution

  • 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';