Problem with ROWs and UNION
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.
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)
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
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