Re: Array comparison & prefix search - Mailing list pgsql-general

Would a GIN index help?  You'd be able to ask if a 'foo' appears
anywhere in the array (or some subset if you want).  You can then have a
subsequent filter that actually expresses the clause you want.  Not sure
what selectivity you're dealing with and if this would be a problem.

Arrays and PG (not sure how well other databases handle this case
either) don't work too well.  Have you thought about normalising your
schema a bit to give the database more help?

I'd say ROW is doing the wrong thing here, but I think other people may
well disagree with me.  Composite/non-atomic types don't exist in the
SQL spec much (AFAIK) hence their behavior is somewhat ad-hoc and tends
to reflect the original use case rather than being too consistent.

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.