
 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=> 


... hmm, any suggestion?

						Zakkr



Mon, 27 Sep 1999 15:11:10 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
_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 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, Pennsylvania 19026

Mon, 27 Sep 1999 11:42:33 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] _text problem in union
> 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, Pennsylvania 19026

Mon, 27 Sep 1999 12:51:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] _text problem in union
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

Mon, 27 Sep 1999 19:56:42 +0300
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] _text problem in union
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

Mon, 27 Sep 1999 19:30:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] _text problem in union 
> > 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

Tue, 28 Sep 1999 01:32:39 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] _text problem in union
> 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, Pennsylvania 19026

Mon, 27 Sep 1999 21:38:28 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] _text problem in union


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 :-))


Tue, 28 Sep 1999 09:26:12 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] _text problem in union 