Thread: Avoid "could not determine interpretation of row comparisonoperator ="
Avoid "could not determine interpretation of row comparisonoperator ="
From
"Voillequin, Jean-Marc"
Date:
Hello,
When I run the following sql:
create or replace function cmp_numeric_text(a numeric, b text) returns boolean as $$ select a=b::numeric;$$ language sql strict immutable;
create operator = (leftarg = numeric, rightarg = text, procedure = cmp_numeric_text);
select 'OK' where (1,2) in (select '1'::text,'2'::text);
I get:
ERROR: could not determine interpretation of row comparison operator =
LINE 1: select 'OK' where (1,2) in (select '1'::text,'2'::text);
^
HINT: Row comparison operators must be associated with btree operator families.
I understand that row (1,2) (int,int) cannot be compared to row (‘1’,’2’) (text,text) without coding a new comparison operator = for row type.
I have read the documentation on “create/alter operator class/family” but could not figure out how to do this.
Is it just possible to implement such feature within PG thanks to operator class/family ?
Of course, the case I have to solve is more complex. But a small example or recommendation will be greatly appreciated.
Thanks.
Regards.
-----------------------------------------
Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its customers, employees and business and where allowed to do so by applicable law. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.
This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
-----------------------------------------
"Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes:
> I understand that row (1,2) (int,int) cannot be compared to row ('1','2') (text,text) without coding a new comparison
operator= for row type.
> I have read the documentation on "create/alter operator class/family" but could not figure out how to do this.
> Is it just possible to implement such feature within PG thanks to operator class/family ?
TBH, I think you would greatly regret that if you did it. It'd create
numerous problems:
* Some queries would start failing due to inability to resolve an
ambiguous operator choice.
* The ordering of numbers and text isn't the same, eg 123 is greater than
23 if you consider them to be numbers but not if you consider them to
be text. If you fuzz the line determining which ordering applies, that's
going to result in a lot of confusion for you, even if the system thinks
it's clear.
* Even simple equality isn't the same in the two domains, consider "1"
vs "+1". More room for surprises.
* The semantics you've chosen here (cast the text input to numeric)
mean that the comparison operator will fail outright for a large
fraction of its possible text inputs. That's seldom a desirable
behavior for a btree comparison operator.
I think you'd be way better off to fix whichever part of your application
is confused enough to think that a query like that is sensible.
> Of course, the case I have to solve is more complex. But a small example or recommendation will be greatly
appreciated.
Perhaps what you're really trying to do is more sensible than
this example, but if so you should give us a less oversimplified
example.
regards, tom lane
RE: Avoid "could not determine interpretation of row comparisonoperator ="
From
"Voillequin, Jean-Marc"
Date:
Tom,
I'm very proud to have such explanations from you. It's clear.
To be honest, I was not very "comfortable" with this solution.
I will fix the original code by casting.
I'm currently migrating 646340 lines of Oracle PL/SQL code to plpgsql and this point was one of the last issues.
Thanks and regards.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, November 30, 2018 4:31 PM
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Avoid "could not determine interpretation of row comparison operator ="
CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the
senderand know the content is safe.
"Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes:
> I understand that row (1,2) (int,int) cannot be compared to row ('1','2') (text,text) without coding a new comparison
operator= for row type.
> I have read the documentation on "create/alter operator class/family" but could not figure out how to do this.
> Is it just possible to implement such feature within PG thanks to operator class/family ?
TBH, I think you would greatly regret that if you did it. It'd create numerous problems:
* Some queries would start failing due to inability to resolve an ambiguous operator choice.
* The ordering of numbers and text isn't the same, eg 123 is greater than
23 if you consider them to be numbers but not if you consider them to be text. If you fuzz the line determining which
orderingapplies, that's going to result in a lot of confusion for you, even if the system thinks it's clear.
* Even simple equality isn't the same in the two domains, consider "1"
vs "+1". More room for surprises.
* The semantics you've chosen here (cast the text input to numeric) mean that the comparison operator will fail
outrightfor a large fraction of its possible text inputs. That's seldom a desirable behavior for a btree comparison
operator.
I think you'd be way better off to fix whichever part of your application is confused enough to think that a query like
thatis sensible.
> Of course, the case I have to solve is more complex. But a small example or recommendation will be greatly
appreciated.
Perhaps what you're really trying to do is more sensible than this example, but if so you should give us a less
oversimplifiedexample.
regards, tom lane
-----------------------------------------
Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its
customers,employees and business and where allowed to do so by applicable law. The information contained in this e-mail
message,and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are
notthe intended recipient or an employee or agent responsible for delivering this message to the intended recipient,
youare hereby notified that you have received this message in error and that any review, dissemination, distribution or
copyingof this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received
thismessage in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its
attachments.Every effort is made to keep our network free from viruses. You should, however, review this e-mail
message,as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any
computervirus which may be transferred via this e-mail message.
This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
-----------------------------------------