Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 29CAB630861
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 12 May 2009 05:08:29 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 60822-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 12 May 2009 05:08:17 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.168])
	by mail.postgresql.org (Postfix) with ESMTP id 805236324E7
	for <pgsql-sql@postgresql.org>; Tue, 12 May 2009 05:05:29 -0300 (ADT)
Received: by wf-out-1314.google.com with SMTP id 28so2482488wfc.28
	for <pgsql-sql@postgresql.org>; Tue, 12 May 2009 01:05:28 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:message-id:subject
	:from:to:content-type;
	bh=IwWcVcAO872zTxNu0iFXNAFQzoOt9gn9jzZL5edJdIc=;
	b=XwAcDFafjNTKBEw7YCsX/Rfs/5CDN8Aj+er8UuAiI5DpH+gnERLKU36EiVB0xczToh
	rV/4e8as2ge17BB6S51R5/L+ThnoE4r3+x/he6LYGQ526iYllLGDakIJ0fdTUa5+OmuC
	asTdNd4bXIN9Dt+tu0r1z46HijLraUDRbOWFw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=Yd1eJGEEBL73UowEzc+exYKlyqwgesfjeVsS43MCwM9QdAnI9zPOMqo2Pyd+iBvGVm
	ChB74Okjho/fKGlsOAfoVYCgWBIH8+dijxtAD7Ydwx0CPe17ya4EzIjg85KK0zenf4ru
	yKWNWJYRQJiZYBU7+3LeIi6OBFjuqMpINIdnM=
MIME-Version: 1.0
Received: by 10.142.230.9 with SMTP id c9mr3530307wfh.101.1242115528400; Tue,
	12 May 2009 01:05:28 -0700 (PDT)
Date: Tue, 12 May 2009 15:05:28 +0700
Message-ID: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
Subject: Array: comparing first N elements?
From: David Garamond <davidgaramond@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0cd17d6ad721c60469b28e2c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200905/52
X-Sequence-Number: 32544

--000e0cd17d6ad721c60469b28e2c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I have a "materialized path" tree table like this (simplified):

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    parents INT[] NOT NULL,
    name TEXT NOT NULL,
    UNIQUE (parents, name)
);
CREATE INDEX name ON product(name);

Previously I use TEXT column for parents, but arrays look interesting and
convenient so I'm considering migrating to arrays. However, how do I rewrite
this using arrays?

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

In other words, testing against the first N elements in an array.

Regards,
Dave

--000e0cd17d6ad721c60469b28e2c
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>I have a &quot;materialized path&quot; tree table like this (simplifie=
d):</div><div><br></div><div>CREATE TABLE product (<br>=A0 =A0 id SERIAL PR=
IMARY KEY,<br>=A0 =A0 parents INT[] NOT NULL,<br>=A0 =A0 name TEXT NOT NULL=
,<br>=A0 =A0 UNIQUE (parents, name)<br>
);<br>CREATE INDEX name ON product(name);<br></div><div><br></div><div>Prev=
iously I use TEXT column for parents, but arrays look interesting and conve=
nient so I&#39;m considering migrating to arrays. However, how do I rewrite=
 this using arrays?</div>
<div><br></div><div>SELECT * FROM product </div><div>WHERE parents LIKE &#3=
9;0001/0010/%&#39;;=A0</div><div><br></div><div>In other words, testing aga=
inst the first N elements in an array.</div><div><br></div><div>Regards,</d=
iv>
<div>Dave=A0</div>

--000e0cd17d6ad721c60469b28e2c--