Search code examples
sqlpostgresqlltree

Postgres - Counting children in level (below) using ltree

I have a table

CREATE TABLE foo (text name, path ltree);

A couple of inserts

INSERT INTO foo (name, path) VALUES ( 'Alice', 'ROOT.first.parent');
INSERT INTO foo (name, path) VALUES ( 'Bob', 'ROOT.second.parent');
INSERT INTO foo (name, path) VALUES ( 'Mistress', 'ROOT.third.parent');
INSERT INTO foo (name, path) VALUES ( 'Ted', 'ROOT.first.parent.child');
INSERT INTO foo (name, path) VALUES ( 'Carol', 'ROOT.second.parent.child');

Now I simply want to count the nodes under ROOT. I think I should do:

SELECT count(path) FROM foo
WHERE path ~ 'ROOT.*{1}'

I would expect 3 but I get 0. Any ideas?

Solution

  • Fount it! As with all things, RTFM is not a bad advice. The bundled Subpath function does the trick. Count distinct occurrences from offset 1, length 1:

    select count(distinct subpath(path, 1, 1))
from foo

    3.