Thread: 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."
> 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."
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!