Re: Array comparison & prefix search

On Fri, Dec 04, 2009 at 06:58:21PM +0100, Denes Daniel wrote:
> SELECT * FROM test WHERE type = 'two' AND ident[1] = 'test';
>
> 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:

I think you want to create a functional index on ident[1], something
like:

CREATE INDEX test_my_idx ON test (type,(ident[1]));

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

The semantics of this are somewhat fuzzy; I think the behavior is
caused by the fact that the value "as a whole" isn't NULL, hence you get
a non-null result. You only get a NULL result when the "whole" value is
null, hence values of integer type either have a value or they're null.
As you see, for values of non-atomic type it gets a bit more awkward and
there are various opinions about how they "should" be handled.

--
Sam http://samason.me.uk/