Re: Tree structure

Kaare Rasmussen <kaare(at)jasonic(dot)dk> writes:

> Hi
>
> I'm trying to determine the best way to represent a simple tree
> structure (like a file/dir tree or a uri path). I guess that's done a
> zillion times before; I just don't seem to be able to find the right
> solution. I have one special request, that I'd like to find all
> shorter' paths, i.e. given 'a/b/c/d' it'll find
>
> a
> a/b
> a/b/c
> - but not
> b
> a/c
> b/a

If I understand you correctly, you want a prefix match, and sure there's
a PostgreSQL extension for that:

CREATE EXTENSION prefix;

CREATE TABLE t1 (
id serial NOT NULL,
p prefix_range NOT NULL,
PRIMARY KEY (id)
);

CREATE INDEX pp ON t1 USING gist(p);

INSERT INTO t1 (p) VALUES
('a'),
('b'),
('a/c'),
('a/b'),
('b/a'),
('a/b/c');

EXPLAIN ANALYZE
SELECT id, p
FROM t1
WHERE p @> 'a/b/c/d'
;