Re: Array Comparison

Ian Harding wrote
> On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <

> harding.ian@

> > wrote:
>> I have a function that returns bigint[] and would like to be able to
>> compare a bigint to the result.

Here are some of your options:

http://www.postgresql.org/docs/9.3/interactive/functions-array.html
http://www.postgresql.org/docs/9.3/interactive/functions-comparisons.html

The direct type-to-type operators are covered in the first link while
generic comparison mechanisms - including those the can compare arrays to
scalars - are in the second one.

There are lots of ways to compare things; e.g., are they equal, is one
greater than another and, for multi-valued items, does one contain the other
or do they overlap

>> select 935::bigint in (select
>> fn_descendents('trip'::varchar,61::bigint));
>> ERROR: operator does not exist: bigint = bigint[]

As shown by the error the application of "IN" simply checks to see if any of
the ROWS of the given select match against the left-hand value. That means
zero or more evaluations of:
bigint = bigint[]
which does not makes sense. There is no special evalulation mode for a
subquery that only happens to return a single row.

From the second link above you can express the scalar-to-array comparison
you seek through the use of "ANY".

bigint = ANY(bigint[])

Since your function already returns an array you do not to (and indeed
cannot) use a subquery/SELECT. Simply write:

935::bigint = ANY(fn_descendents(...))

>> Hmmm.. This works...
>>
> select array[935::bigint] <@ (select
> fn_descendents('trip'::varchar,61::bigint));
>
> Still, why?

Do you understand the concept of array containment - what it means for an
array to contain or be contained by another array? The documentation
assumes that concept is known and simply provides the syntax/operators
needed to access it.

David J.

--
View this message in context: http://postgresql.nabble.com/Array-Comparison-tp5829471p5829473.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.