From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Kaare Rasmussen <kaare(at)jasonic(dot)dk> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Tree structure |
Date: | 2013-09-20 12:55:19 |
Message-ID: | CAF-3MvNaS0QxnErdA096zn-KVhnyDWvWSsdF7h7vQ=+F0QDx1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
4. Using a recursive common table expression (CTE).
http://www.postgresql.org/docs/9.2/static/queries-with.html
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2013-09-20 13:59:55 | Re: reading cvs logs with pgadmin queries |
Previous Message | Tom Fraser | 2013-09-20 12:25:14 | Postgres 9.3 Installation Problems Windows 7 64 Bit Pro |