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