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
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 |