Re: Array comparison & prefix search

without digging too much into the details - just a suggestion:

look at the ltree contrib. it actually provides an indexable array
prefix search.

2009/12/4 Denes Daniel <panther-d(at)freemail(dot)hu>:
> 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?
>
> Regards,
> Denes Daniel

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/