Array comparison & prefix search

From: Denes Daniel <panther-d(at)freemail(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: Array comparison & prefix search
Date: 2009-12-04 17:58:21
Message-ID: cd515af0912040958j18947d4ch5a6d236dc35c5479@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2009-12-04 18:10:35 Re: Array comparison & prefix search
Previous Message Kern Sibbald 2009-12-04 17:46:37 Re: Catastrophic changes to PostgreSQL 8.4