David G Johnston <david.g.johnston@gmail.com> writes:
> On Thu, Jul 31, 2014 at 12:19 AM, Adam Mackler-5 [via PostgreSQL] <
> ml-node+s1045698n5813399h93@n5.nabble.com> wrote:
>> Can you explain what the abuse is? Also why the "=" operator does not
>> work even without the domain?
> When you write, explicitly, "domain = text" the operator is directly found.
> When you write, implicitly, "domain = unknown" there is no operator and so
> the system has to go looking. It decides that the operator least likely to
> cause a problem is the "text = text" (varchar, char(3), text - these are
> all implemented in the same manner and are effectively synonyms).
There are a couple of reasons why this operator doesn't get selected:
1. Unless there's an exact match out-of-the-box, the operator resolution
code's second step is to smash domains to base types. So
"my_domain = unknown" is reduced to "char = unknown" and from there
you're going to end up with the char = char operator.
2. text is the preferred type among the string class, so any case where
you have text on one side and some other string type on the other is
going to get resolved as text vs text.
There's documentation about this at
http://www.postgresql.org/docs/9.3/static/typeconv-oper.html
although I see that it omits to mention the domain-flattening step.
Because of #1, domain-specific functions and operators tend to be pretty
useless; you find yourself always having to cast the other side to get
an exact match. There's been discussion about changing that, but it's
not clear how to do it without breaking the fact that usually you *do*
want the base type's operators to apply to the domain.
If you're intent on having this behavior, the way to go at it is to make
your own actual datatype (not a domain) and create all your own comparison
operators for it. You can add an implicit cast to text for cases where
you don't want to define your own operator. See contrib/citext for an
example.
(BTW, I wonder whether you could solve your problem without a pile of
C programming by defining a domain over citext.)
regards, tom lane
