Kaare Rasmussen <kaare@jasonic.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'
;
