On Fri, Dec 4, 2009 at 12:58 PM, Denes Daniel <panther-d@freemail.hu> wrote:
> Hi,
>
> I have a table like this:
>
> CREATE TABLE test (
> type text NOT NULL,
> ident text[] NOT NULL,
> ...
> );
> ALTER TABLE ONLY test ADD CONSTRAINT test_pkey PRIMARY KEY (type, ident);
>
> and I would like to query rows that have a specific "type" and whose "ident"
> array starts with a some given constants.
> I mean something like this:
>
> INSERT INTO test VALUES ('one', ARRAY['string']);
> INSERT INTO test VALUES ('two', ARRAY['tab', 'str1']);
> INSERT INTO test VALUES ('two', ARRAY['test', 'str1']);
> INSERT INTO test VALUES ('two', ARRAY['test', 'str2']);
> INSERT INTO test VALUES ('two', ARRAY['try', 'str1']);
> INSERT INTO test VALUES ('three', ARRAY['some', 'more', 'strings']);
>
> SELECT * FROM test WHERE type = 'two' AND ident[1] = 'test';
>
> But this query uses the primary key index only for the "type" field, and
> then filters for ident[1]. Is there a way to make it use the index for the
> array prefix search too, like with " textcol LIKE '123%' " ? The only way I
> can think of, is this:
>
> SELECT * FROM test WHERE type = 'two' AND (ident >= ARRAY['test', ''] AND
> ident <= ARRAY['test', NULL]);
>
> This uses the index as much as possible, so it's fast, and gives correct
> results. But something's strange, because it's based on the thing that all
> strings are greather than or equal to the empty string, and all are less
> than or equal to NULL... which is fine when ordering rows, so it's fine too
> in the B-tree (I think), but shouldn't it return no rows, because ('string'
> <= NULL) is NULL?
>
> In fact, ('string' <= NULL) is NULL if I test it directly, or use row-wise
> comparison, but when I use array comparison, NULL is greather than 'string'.
> SELECT 'string' <= NULL::text, ARRAY['string'] <= ARRAY[NULL::text];
> This gives me a NULL and a TRUE.
> Why? Can I rely on this? If I can't, is there another way to make the array
> prefix search use the index?
AFAIK, your approach is the only solution given your requirements. It
works well...I've used it often, but usually for integers. Maybe
there is a missing operator for arrays kinda similar to the contains
operator that would be btree indexable.
merlin
