Search Postgresql Archives

Re: Array comparison & prefix search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Dec 4, 2009 at 12:58 PM, Denes Daniel <panther-d@xxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux