Re: Array comparison & prefix search

2009/12/4 Sam Mason <sam(at)samason(dot)me(dot)uk>

> I think you want to create a functional index on ident[1], something
> like:
>
> CREATE INDEX test_my_idx ON test (type,(ident[1]));

Sorry, but this approach is no good, since I may search like:
SELECT * FROM test WHERE type = 'three' AND (ident[1] = 'foo' AND ident[2] =
'bar');
or for the first 3 items in an array with 6 items, or any other prefix...

The arrays are all the same length for a given type, but for type
'twenty-three' they may be 23 items long, or even longer for another type,
so I can't create an index for all possible cases that way. And yet, all the
information needed is in the primary index, I just don't know how to get
PostgeSQL to use it.

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

I see, but the documentation says: "Array comparisons compare the array
contents element-by-element, [...]". So, if we compare two arrays, where the
first difference is this 'string' / NULL thing, then we will reach a point
(after comparing all those items that are equal) where 'string' compares to
NULL, and the result is that NULL is greater. At least that's the only way I
can think of, how I'd get this TRUE result. So is NULL really greater than
all other text?
And why is it this way when I'm using an ARRAY[], and the other way when
using ROW()?

SELECT ARRAY['abc', 'string', 'z'] < ARRAY['abc', NULL::text, 'a'];
--> returns TRUE
SELECT ROW('abc', 'string', 'z') < ROW('abc', NULL::text, 'a');
--> returns NULL

Regards,
Denes Daniel