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
