"ERROR: operator is not unique" with Custom Data Type
Howdy,
I'm working on a custom data type based on TEXT that does case-
insensitive, locale-aware comparisons, essentially by calling LOWER()
to compare values. I'll have more to ask about this later, when I want
to get feedback on the implementation. But right now I'm just writing
tests and trying to get it all to work the way I think it should.
So I've implemented operators and an operator class for the new type,
and they work great. I've also added implicit casts between the other
string data types:
CREATE CAST (lctext AS text) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS lctext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (lctext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (varchar AS lctext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (lctext AS bpchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (bpchar AS lctext) WITHOUT FUNCTION AS IMPLICIT;
However, thanks to the implicit cast PostgreSQL finds more than one
candidate operator when I compare properly casted values:
try=# select 'a'::lctext = 'a'::text;
ERROR: operator is not unique: lctext = text
LINE 1: select 'a'::lctext = 'a'::text;
^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
So is there a way to resolve this? Would I need to add explicit
operators between lctext and text (and more, betwein text and lctext),
assuming that PostgreSQL would find those to be the best candidate
operators?
I'm kind of hoping that there's a simpler answer, because otherwise
I'd have to create operators and classes for all of:
( lctext, lctext )
( lctext, text )
( text, lctext )
( lctext, lctext )
( lctext, varchar )
( varchar, lctext )
( lctext, bpchar )
( bpchar, lctext )
And then I supposed that I'd have to do the same not only for the
comparison operators in the operator class, but also any other binary
operators (concatenation, regular expression, LIKE, etc.). This sounds
like somewhat of a PITA, though I'd of course just do the cut-and-
paste work to make it so if that was what's required. But is it? Is
there no simpler way to do it?
Many thanks,
David
On Thu, Jun 05, 2008 at 11:18:26AM -0700, David E. Wheeler wrote:
I'm working on a custom data type based on TEXT that does case-
insensitive, locale-aware comparisons, essentially by calling LOWER()
to compare values.
What makes this different from the citext project?
However, thanks to the implicit cast PostgreSQL finds more than one
candidate operator when I compare properly casted values:try=# select 'a'::lctext = 'a'::text;
ERROR: operator is not unique: lctext = text
LINE 1: select 'a'::lctext = 'a'::text;
^
What would you want postgresql to choose in this case. Whichever way
you want it, make that direction implicit and the other direction
assignment. Having A->B and B->A both as implicit just leads to
ambiguity.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
On Jun 5, 2008, at 11:28, Martijn van Oosterhout wrote:
On Thu, Jun 05, 2008 at 11:18:26AM -0700, David E. Wheeler wrote:
I'm working on a custom data type based on TEXT that does case-
insensitive, locale-aware comparisons, essentially by calling LOWER()
to compare values.What makes this different from the citext project?
citext is not locale-aware; please Tom's comments in the "Case-
Insensitve Text Comparison" thread.
However, thanks to the implicit cast PostgreSQL finds more than one
candidate operator when I compare properly casted values:try=# select 'a'::lctext = 'a'::text;
ERROR: operator is not unique: lctext = text
LINE 1: select 'a'::lctext = 'a'::text;
^What would you want postgresql to choose in this case.
I was thinking that the ::text should be cast to ::lctext, as that's
how `'a'::lctext = 'a'` works, but I keep going back and forth in my
mind. Maybe 'a'::lctext should not equal 'A'::text.
Whichever way
you want it, make that direction implicit and the other direction
assignment.
I'm sure I'm missing something simple here. How do I make it assignment?
Having A->B and B->A both as implicit just leads to
ambiguity.
Huh. That's what citext has, too:
CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS citext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (bpchar AS citext) WITHOUT FUNCTION AS IMPLICIT;
But I agree that there is confusion for PostgreSQL here.
Thanks,
David
"David E. Wheeler" <david@kineticode.com> writes:
On Jun 5, 2008, at 11:28, Martijn van Oosterhout wrote:
What would you want postgresql to choose in this case.
I was thinking that the ::text should be cast to ::lctext, as that's
how `'a'::lctext = 'a'` works, but I keep going back and forth in my
mind. Maybe 'a'::lctext should not equal 'A'::text.
It seems to me that lctext is sort of like a more-constrained version
of text (like a domain), which suggests that the lctext -> text
direction can be implicit but the other direction should not be.
Moreover, if you don't have lctext -> text be implicit then you
will find that none of the non-comparison text functions work on
lctext except with a cast; which is not the place you want to be.
I concur with Martijn that having both directions implicit is a
Bad Idea.
BTW, I would encourage you to think of this project as citext version 2,
rather than inventing a new name for the datatype. All you'll
accomplish with that is make it hard for users of citext to transition.
regards, tom lane
On Jun 5, 2008, at 11:51, Tom Lane wrote:
I was thinking that the ::text should be cast to ::lctext, as that's
how `'a'::lctext = 'a'` works, but I keep going back and forth in my
mind. Maybe 'a'::lctext should not equal 'A'::text.It seems to me that lctext is sort of like a more-constrained version
of text (like a domain),
Yes, exactly.
which suggests that the lctext -> text
direction can be implicit but the other direction should not be.
Ah, okay. That's a good way of putting it. So I should just eliminate
the implicit text -> lctext cast, then? That will solve the problem?
Moreover, if you don't have lctext -> text be implicit then you
will find that none of the non-comparison text functions work on
lctext except with a cast; which is not the place you want to be.
No, quite right.
I concur with Martijn that having both directions implicit is a
Bad Idea.BTW, I would encourage you to think of this project as citext
version 2,
rather than inventing a new name for the datatype. All you'll
accomplish with that is make it hard for users of citext to
transition.
Fair enough. It was a working title, anyway.
Best,
David
On Thu, Jun 05, 2008 at 11:37:28AM -0700, David E. Wheeler wrote:
Whichever way
you want it, make that direction implicit and the other direction
assignment.I'm sure I'm missing something simple here. How do I make it assignment?
# \h create cast
Command: CREATE CAST
Description: define a new cast
Syntax:
<snip>
CREATE CAST (sourcetype AS targettype)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
Huh. That's what citext has, too:
CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS citext) WITHOUT FUNCTION AS IMPLICIT;
And citext probably doesn't work with 8.3? The casting rules wrt text
have changed...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
On Jun 5, 2008, at 14:07, Martijn van Oosterhout wrote:
I'm sure I'm missing something simple here. How do I make it
assignment?# \h create cast
Command: CREATE CAST
Description: define a new cast
Syntax:
<snip>
CREATE CAST (sourcetype AS targettype)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
I need to read up on the CAST documentation. Thanks.
Huh. That's what citext has, too:
CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS citext) WITHOUT FUNCTION AS IMPLICIT;And citext probably doesn't work with 8.3? The casting rules wrt text
have changed...
Yes, that is correct. It builds, but the SQL doesn't all run properly.
I'll be wading through all those failures once I get the basics worked
out with v2.
Thanks,
David