Обсуждение: _text problem in union

Hi,
I have small problem with text array in union query..     see:


abil=> select 5 union select 5;
?column?
--------      5
(1 row)

abil=> select 5 union select 6;
?column?
--------      5      6
(2 rows)

abil=> select '{"aaa"}'::_text union select '{"aaa"}'::_text;
?column?
--------
{"aaa"} 
(1 row)

abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
ERROR:  Unable to identify an ordering operator '<' for type '_text'       Use an explicit ordering operator or modify
thequery
 
abil=> 


... hmm, any suggestion?
                    Zakkr

> 
>  Hi,
> 
>  I have small problem with text array in union query..     see:
> 
> 
> abil=> select 5 union select 5;
> ?column?
> --------
>        5
> (1 row)
> 
> abil=> select 5 union select 6;
> ?column?
> --------
>        5
>        6
> (2 rows)
> 
> abil=> select '{"aaa"}'::_text union select '{"aaa"}'::_text;
> ?column?
> --------
> {"aaa"} 
> (1 row)
> 
> abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> ERROR:  Unable to identify an ordering operator '<' for type '_text'
>         Use an explicit ordering operator or modify the query
> abil=> 

Good problem description.  Seems we can't compare arrays of text fields.

Seems if we have an array of text fields, we could compare each element
one-by-one using the base type until we get a comparison result.

Not sure if this should make the TODO list or not.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian wrote:
>
> > abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> > ERROR:  Unable to identify an ordering operator '<' for type '_text'
> >         Use an explicit ordering operator or modify the query
> > abil=>
>
> Good problem description.  Seems we can't compare arrays of text fields.
>
> Seems if we have an array of text fields, we could compare each element
> one-by-one using the base type until we get a comparison result.
>
> Not sure if this should make the TODO list or not.

It woulf be better to have a generic array compare op, that just
traverses
both arrays comparing them with the "<" for base type

--------------
Hannu

> Bruce Momjian wrote:
> > 
> > > abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> > > ERROR:  Unable to identify an ordering operator '<' for type '_text'
> > >         Use an explicit ordering operator or modify the query
> > > abil=>
> > 
> > Good problem description.  Seems we can't compare arrays of text fields.
> > 
> > Seems if we have an array of text fields, we could compare each element
> > one-by-one using the base type until we get a comparison result.
> > 
> > Not sure if this should make the TODO list or not.
> 
> It woulf be better to have a generic array compare op, that just
> traverses 
> both arrays comparing them with the "<" for base type

Yes, that was my idea.  Is this a worthy TODO item?

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Zakkr <zakkr@zf.jcu.cz> writes:
> abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> ERROR:  Unable to identify an ordering operator '<' for type '_text'
>         Use an explicit ordering operator or modify the query

Depending on what you're trying to do, UNION ALL might be an adequate
workaround.  UNION is defined to remove duplicates, so it has to sort
the results of the union'ed queries, which requires an ordering
operator.  UNION ALL just appends the two query results together...

In the long run we probably ought to think about providing ordering
operators for array types.
        regards, tom lane

> > It woulf be better to have a generic array compare op, that just
> > traverses both arrays comparing them with the "<" for base type
> Yes, that was my idea.  Is this a worthy TODO item?

Sure. There should be a fairly large list of things for arrays, which
have not quite gotten the same attention as other Postgres features.
                - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> Zakkr <zakkr@zf.jcu.cz> writes:
> > abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> > ERROR:  Unable to identify an ordering operator '<' for type '_text'
> >         Use an explicit ordering operator or modify the query
> 
> Depending on what you're trying to do, UNION ALL might be an adequate
> workaround.  UNION is defined to remove duplicates, so it has to sort
> the results of the union'ed queries, which requires an ordering
> operator.  UNION ALL just appends the two query results together...
> 
> In the long run we probably ought to think about providing ordering
> operators for array types.
> 

Added to TODO:
* Allow arrays to be ORDER'ed

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

On Mon, 27 Sep 1999, Tom Lane wrote:

> Zakkr <zakkr@zf.jcu.cz> writes:
> > abil=> select '{"aaa"}'::_text union select '{"bbb"}'::_text;
> > ERROR:  Unable to identify an ordering operator '<' for type '_text'
> >         Use an explicit ordering operator or modify the query
> 
> Depending on what you're trying to do, UNION ALL might be an adequate
> workaround.  UNION is defined to remove duplicates, so it has to sort
> the results of the union'ed queries, which requires an ordering
> operator.  UNION ALL just appends the two query results together...
> 

Yes, UNION ALL is good resolution for me. Thank Tom.

> In the long run we probably ought to think about providing ordering
> operators for array types.
..hmm :-))