From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Array: comparing first N elements?
Date: Tue, 12 May 2009 11:38:32 +0300
User-Agent: KMail/1.9.7
Cc: David Garamond <davidgaramond@gmail.com>
References: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
In-Reply-To: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200905121138.33188.achill@matrix.gatewaynet.com>

=D3=F4=E9=F2 Tuesday 12 May 2009 11:05:28 =EF/=E7 David Garamond =DD=E3=F1=
=E1=F8=E5:
> I have a "materialized path" tree table like this (simplified):
>=20
> CREATE TABLE product (
>     id SERIAL PRIMARY KEY,
>     parents INT[] NOT NULL,
>     name TEXT NOT NULL,
>     UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>=20
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewr=
ite
> this using arrays?
>=20

Hi, I have used *exactly* the same scheme to model all PMS data in out flee=
t comprising
of 1.5 million rows, for some 6 years now.
You may find it in literature as genealogical tree representation.

> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';

Node 0001/0010 will have an id lets call it "parid".
If you model your path in parents[] (we also use the same column name!)
starting from the immediate father at parents[1] and going up to the root a=
t parents[#parents]
then what you actually want is to find all nodes for which parents[1]=3Dpar=
id

you would want to look at the intarray contrib package for index suppor and=
 many other goodies,
also you might want to write fucntions first(parents), last(parents) and th=
en have an index=20
on those as well.
This way searching for the direct children of a node is very fast.

If on the other hand you want to find all children of parid, regardless of =
level,
then you would do that with: intset(parid) ~ parents
=46or the above to be efficient you should create an index on parents. Pref=
er method "gin" with opclass "gin__int_ops"

Well thats how i implemented trees in postgresql anyway.

>=20
> In other words, testing against the first N elements in an array.
>=20
> Regards,
> Dave
>=20


=2D-=20
Achilleas Mantzios