From: | Denes Daniel <panther-d(at)freemail(dot)hu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Array comparison & prefix search |
Date: | 2009-12-05 01:23:13 |
Message-ID: | cd515af0912041723n4664ab3ay1c770de18e70ae4b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-12-05 02:04:41 | Re: Array comparison & prefix search |
Previous Message | Merlin Moncure | 2009-12-04 21:05:13 | Re: Examples of using PQexecParams |