Postgres - Counting children in level (below) using ltree

Question

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?

score 2 · Answer 1 · edited Feb 01 '14 at 11:47

2

You'd need either

WHERE path ~ 'ROOT.*{2}'

or

WHERE path ~ 'ROOT.*.parent'

That's because 'first.parent' is two labels, not one. The second WHERE clause looks for paths that end with 'parent', which I think makes your intent clearer.

You can see the sqlfiddle here.

edited Feb 01 '14 at 11:47

dan-gph

16,301
12
61
79

answered Feb 01 '14 at 02:08

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Let me rephrase: ROOT has n children (3 in this case). I don't know their labels. How do I count them? `WHERE path ~ 'ROOT.*{2}'` is a false positive because Alice, Bob, and Mistress have exactly 3 levels. A correct answer to the wrong question. – Ove Sundberg Feb 03 '14 at 21:23
1

An lquery won't answer that question, but the subpath() function will. You already know that. ;) – Mike Sherrill 'Cat Recall' Feb 03 '14 at 23:04

score 1 · Accepted Answer · answered Feb 03 '14 at 22:58

1

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.

answered Feb 03 '14 at 22:58

Ove Sundberg

333
3
10

Postgres - Counting children in level (below) using ltree

2 Answers2