agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedAvoid "could not determine interpretation of row comparison operator ="
3+ messages / 2 participants
[nested] [flat]
* Avoid "could not determine interpretation of row comparison operator ="
@ 2018-11-30 13:35 Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
2018-11-30 15:31 ` Re: Avoid "could not determine interpretation of row comparison operator =" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Voillequin, Jean-Marc @ 2018-11-30 13:35 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
-----------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Avoid "could not determine interpretation of row comparison operator ="
2018-11-30 13:35 Avoid "could not determine interpretation of row comparison operator =" Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
@ 2018-11-30 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-11-30 16:25 ` RE: Avoid "could not determine interpretation of row comparison operator =" Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2018-11-30 15:31 UTC (permalink / raw)
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Avoid "could not determine interpretation of row comparison operator ="
2018-11-30 13:35 Avoid "could not determine interpretation of row comparison operator =" Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
2018-11-30 15:31 ` Re: Avoid "could not determine interpretation of row comparison operator =" Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-11-30 16:25 ` Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
0 siblings, 0 replies; 3+ messages in thread
From: Voillequin, Jean-Marc @ 2018-11-30 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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 sender and 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 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
-----------------------------------------
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
-----------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-11-30 16:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-30 13:35 Avoid "could not determine interpretation of row comparison operator =" Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
2018-11-30 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-11-30 16:25 ` Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox