Re: Array: comparing first N elements?

On Tue, May 12, 2009 at 4:05 AM, David Garamond <davidgaramond(at)gmail(dot)com> 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