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

Answer

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') ~ '(md*M).*1';