hstore problem with UNION?
I've encountered the following problem:
ivoras=# create table htest2(id integer, t hstore);
CREATE TABLE
ivoras=# create table htest3(id integer, t2 hstore);
CREATE TABLE
ivoras=# select id, t from htest2 union select id,t2 as t from htest3;
ERROR: could not identify an ordering operator for type hstore
HINT: Use an explicit ordering operator or modify the query.
I think it has something to do with UNION being the type of operation
that could, as a variation, include DISTINCT, which would require
sorting, but still... UNION by itself doesn't.
How to get around this? I really don't care how hstores get sorted and
more, would like to avoid sorting them at all as they could get big.
How to get around this? I really don't care how hstores get sorted and
more, would like to avoid sorting them at all as they could get big.
union all
seems to work. Would that serve the purpose?
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
When we do a union, the database has to get rid of duplicates and get
distinct values. To achieve this, probably it does a sort. Just
guesswork....
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
On 05/10/10 14:10, Jayadevan M wrote:
When we do a union, the database has to get rid of duplicates and get
distinct values. To achieve this, probably it does a sort. Just
guesswork....
You are right, it looks like I have inverted the logic of UNION and
UNION ALL - I actually needed "UNION ALL" here, thanks!