Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id F37D0633386 for ; Tue, 12 May 2009 05:43:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25261-01-10 for ; Tue, 12 May 2009 05:43:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.30]) by mail.postgresql.org (Postfix) with ESMTP id 4ED6A635254 for ; Tue, 12 May 2009 05:42:38 -0300 (ADT) Received: by yx-out-2324.google.com with SMTP id 8so1948589yxg.73 for ; Tue, 12 May 2009 01:42:38 -0700 (PDT) MIME-Version: 1.0 Received: by 10.151.102.10 with SMTP id e10mr8341273ybm.340.1242117444563; Tue, 12 May 2009 01:37:24 -0700 (PDT) In-Reply-To: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com> References: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com> Date: Tue, 12 May 2009 04:37:24 -0400 X-Google-Sender-Auth: a37334b6b0d690d2 Message-ID: Subject: Re: Array: comparing first N elements? From: Glenn Maynard To: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200905/56 X-Sequence-Number: 32548 On Tue, May 12, 2009 at 4:05 AM, David Garamond wrote: > 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. SELECT * FROM product WHERE parents[1] = 1 AND parents[2] = 2; I'd expect there to be a way to index this, on individual components or a slice, eg. CREATE INDEX parents_1 ON product(parents[1]); CREATE INDEX parents_2to4 ON product(parents[2], parents[3], parents[4]); ... but this throws a parse error. I don't have an immediate need for this, but I'm curious if this is possible--it seems a natural part of having a native array type. -- Glenn Maynard