Re: Array comparison & prefix search

On Sat, Dec 5, 2009 at 10:31 AM, Denes Daniel <panther-d(at)freemail(dot)hu> wrote:
> According to the documentation,
> http://www.postgresql.org/docs/8.4/static/functions-comparisons.html#ROW-WISE-COMPARISON
> "Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not handled
> per SQL specification."
> I think the way ROW comparisons work now is per SQL specification.
>
> But wait! Thank you for making me read this part of the docs, because I've
> just found what I was looking for, at the very end of the page:
>
>>
>> Note: The SQL specification requires row-wise comparison to return NULL if
>> the result depends on comparing two NULL values or a NULL and a non-NULL.
>> PostgreSQL does this only when comparing the results of two row constructors
>> or comparing a row constructor to the output of a subquery (as in Section
>> 9.20). In other contexts where two composite-type values are compared, two
>> NULL field values are considered equal, and a NULL is considered larger than
>> a non-NULL. This is necessary in order to have consistent sorting and
>> indexing behavior for composite types.
>
>
> I was sure I've read this part of the docs a hundred times, so I've gone
> after why I didn't find this before: this note is new in the 8.4 docs, it
> wasn't there before (and I'm using 8.3).
> http://www.postgresql.org/docs/8.3/static/functions-comparisons.html#ROW-WISE-COMPARISON
> But I'm pretty sure now that I can rely on this.

Note, composite types != arrays. Being able to index composite types
is new to 8.4 (you have been able to do arrays for longer than that).
Postgres handling of nullls in composite types is pretty funky, but
nulls being highval in arrays for indexing purposes is probably pretty
safe to rely on.

merlin