Tree structure

From: Kaare Rasmussen <kaare(at)jasonic(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Tree structure
Date: 2013-09-20 11:29:29
Message-ID: 523C3199.4080306@jasonic.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

There are a number of options to test.

1. As strings
There's no dedicated function (@>)
WHERE clause should read something like 'a/b/c/d' LIKE column || '%',
which is both ugly and (I guess) non indexable
Perhaps regex indexes would work, but not efficient and not optimal

2. As array of strings
My favorite, would be elegant. A GIN index on the whole array would
make for fast performance
Alas @> treats the arrays as a set, not an array
WHERE col @> 'a/b/c/d' would find all of the above rows, including a,
a/c, b/a, etc.

3. ltree contrib
The only option that actually works and uses index
@> works as I want it to.
But the single segments can only be alphanumeric and underscore
ltree only supports GIST
there's a length limit.

The last option is the one I'm using right now, but I hope that somebody
can point out where I'm wrong with regards to the other options, or tell
me that there is a better solution somewhere I didn't look.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2013-09-20 11:38:32 Re: Using oracle stored procedures for postgresql database
Previous Message Jayadevan M 2013-09-20 10:08:13 Re: Using oracle stored procedures for postgresql database