Thread: "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/ > 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/ > 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