Thread: UNION and array types

UNIONs don't appear to work when you are trying to union 2 tables that
include array types.
Example:
create table foo (bar int8[]);
insert into foo (bar) values ('{1,2,3}');
select * from foo union select * from foo;
ERROR:  Unable to identify an ordering operator '<' for type 'bigint[]'
         Use an explicit ordering operator or modify the query

Is it possible to make this work?  Please tell me yes.  :)

eric

On Fri, Nov 22, 2002 at 16:29:07 -0500,
  "Eric B. Ridge" <ebr@tcdi.com> wrote:
> UNIONs don't appear to work when you are trying to union 2 tables that
> include array types.
> Example:
> create table foo (bar int8[]);
> insert into foo (bar) values ('{1,2,3}');
> select * from foo union select * from foo;
> ERROR:  Unable to identify an ordering operator '<' for type 'bigint[]'
>         Use an explicit ordering operator or modify the query
>
> Is it possible to make this work?  Please tell me yes.  :)

I think you can get by with union all if you don't care if duplicates stay.

On Friday, November 22, 2002, at 04:50  PM, Bruno Wolff III wrote:
> On Fri, Nov 22, 2002 at 16:29:07 -0500,
>   "Eric B. Ridge" <ebr@tcdi.com> wrote:
>> UNIONs don't appear to work when you are trying to union 2 tables that
>> include array types.
>>
>> Is it possible to make this work?  Please tell me yes.  :)
>
> I think you can get by with union all if you don't care if duplicates
> stay.

Thanks!  This works like a charm.  And the duplicates get filtered out
from an outer SELECT DISTINCT ON (id) query, so this is perfect.

I suppose it's faster too since UNION ALL doesn't try to sort?

eric

Eric B.Ridge wrote:
> I suppose it's faster too since UNION ALL doesn't try to sort?

Should be. Better to use "union all" when the implicit distinct
operation is unnecessary.

Scott