Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with ROWs and UNION

Problem with ROWs and UNION

From
elein@varlena.com (elein)
Date:
Problem with ROW types in UNION.

1. UNION of ROW types fails with operator error.

create type addrs as (
    addr text,
    city    text,
    state    char(2),
    zip    text);

--
-- UNION of ROWS doesn't work (simply wrong)
--
select ROW('4514 Cherry St','Oakland','CA','94666')::addrs
UNION
select ROW('4515 Cherry St','Oakland','CA','94666')::addrs;
-- ERROR:  could not identify an ordering operator for type addrs
-- HINT:  Use an explicit ordering operator or modify the query.

--
-- But select * containing rows does work.
--
create table people (
    name    text,
    fname    text,
    addr    addrs
);
insert into people values ('ae','aem',ROW('4514 Cherry St','Oakland','CA','94666') );
insert into people values ('go','ggo',ROW('4515 Cherry St','Oakland','CA','94666') );

select * from people;
 name | fname |                addr
------+-------+-------------------------------------
 ae   | aem   | ("4514 Cherry St",Oakland,CA,94666)
 go   | ggo   | ("4515 Cherry St",Oakland,CA,94666)
(2 rows)


=============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com
(510)655-2584(o)                             (510)543-6079(c)
          PostgreSQL Consulting, Support & Training

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
==============================================================
I have always depended on the [QA] of strangers.

Re: Problem with ROWs and UNION

From
Alvaro Herrera
Date:
On Thu, May 12, 2005 at 12:32:48PM -0700, elein wrote:

> --
> -- UNION of ROWS doesn't work (simply wrong)
> --
> select ROW('4514 Cherry St','Oakland','CA','94666')::addrs
> UNION
> select ROW('4515 Cherry St','Oakland','CA','94666')::addrs;
> -- ERROR:  could not identify an ordering operator for type addrs
> -- HINT:  Use an explicit ordering operator or modify the query.

UNION ALL does work:

alvherre=# select ROW('4514 Cherry St','Oakland','CA','94666')::addrs
UNION ALL
select ROW('4515 Cherry St','Oakland','CA','94666')::addrs;
                 row
-------------------------------------
 ("4514 Cherry St",Oakland,CA,94666)
 ("4515 Cherry St",Oakland,CA,94666)
(2 filas)


I think the error message is misleading though, because the user has no
way to specify the ordering operator to UNION.

I think you could create a btree operator class to make it all work.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

Re: Problem with ROWs and UNION

From
elein@varlena.com (elein)
Date:
On Thu, May 12, 2005 at 03:57:18PM -0400, Alvaro Herrera wrote:
> On Thu, May 12, 2005 at 12:32:48PM -0700, elein wrote:
>
> > --
> > -- UNION of ROWS doesn't work (simply wrong)
> > --
> > select ROW('4514 Cherry St','Oakland','CA','94666')::addrs
> > UNION
> > select ROW('4515 Cherry St','Oakland','CA','94666')::addrs;
> > -- ERROR:  could not identify an ordering operator for type addrs
> > -- HINT:  Use an explicit ordering operator or modify the query.
>
> UNION ALL does work:
>
> alvherre=# select ROW('4514 Cherry St','Oakland','CA','94666')::addrs
> UNION ALL
> select ROW('4515 Cherry St','Oakland','CA','94666')::addrs;
>                  row
> -------------------------------------
>  ("4514 Cherry St",Oakland,CA,94666)
>  ("4515 Cherry St",Oakland,CA,94666)
> (2 filas)

This makes sense because the UNION needs to have the ordering/
comparative operators to eliminate duplicates.

It is a work around, but I think there needs to be a ROW
ordering operator added.

>
>
> I think the error message is misleading though, because the user has no
> way to specify the ordering operator to UNION.
>
> I think you could create a btree operator class to make it all work.
>


--elein
elein@varlena.com

Re: Problem with ROWs and UNION

From
Tom Lane
Date:
Alvaro Herrera <alvherre@surnet.cl> writes:
> I think you could create a btree operator class to make it all work.

Hm.  Given that we've managed to build a general opclass for arrays,
I suppose it should be possible for records too.  Hardly trivial though.

A closely related point is fixing row comparisons to obey the SQL
semantics properly.

            regards, tom lane