agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: 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 ="
Date: Fri, 30 Nov 2018 10:31:00 -0500
Message-ID: <17747.1543591860@sss.pgh.pa.us> (raw)
In-Reply-To: <1EC8157EB499BF459A516ADCF135ADCE39FFD3A4@LON-WGMSX712.ad.moodys.net>
References: <1EC8157EB499BF459A516ADCF135ADCE39FFD3A4@LON-WGMSX712.ad.moodys.net>
"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
view thread (3+ messages) latest in thread
Message-ID: <17747.1543591860@sss.pgh.pa.us>
Permalink: ../17747.1543591860@sss.pgh.pa.us/
Also on: postgresql.org/message-id/17747.1543591860@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Jean-Marc.Voillequin@moodys.com, pgsql-sql@lists.postgresql.org
Subject: Re: Avoid "could not determine interpretation of row comparison operator ="
In-Reply-To: <17747.1543591860@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox