From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>
cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: Avoid "could not determine interpretation of row comparison
 operator ="
In-reply-to: 
 <1EC8157EB499BF459A516ADCF135ADCE39FFD3A4@LON-WGMSX712.ad.moodys.net>
References: 
 <1EC8157EB499BF459A516ADCF135ADCE39FFD3A4@LON-WGMSX712.ad.moodys.net>
Comments: In-reply-to "Voillequin,
 Jean-Marc" <Jean-Marc.Voillequin@moodys.com>
	message dated "Fri, 30 Nov 2018 13:35:27 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17746.1543591860.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 30 Nov 2018 10:31:00 -0500
Message-ID: <17747.1543591860@sss.pgh.pa.us>
Precedence: bulk

"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 =3D for row type.
> I have read the documentation on "create/alter operator class/family" bu=
t could not figure out how to do this.
> Is it just possible to implement such feature within PG thanks to operat=
or 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