Re: Implementing DB2's "distinct" types

On 2013.04.22 12:09 AM, Thomas Kellerer wrote:
>>> create type sno as varchar(50)
>>> with comparisons;
>>>
>>> create type pno as varchar(50)
>>> with comparisons;
>>>
>>> The following query will be rejected because sno and pno are not comparable
>>> (even though both are varchar columns):
>>>
>>> select *
>>> from p
>>> join s on s.sno = p.pno;
>>>
> I'm more interested if this can be dealt with on SQL level, rather than hacking
> Postgres itself
> (and it's not really a "request" for a new feature - I'm just curious)

To have proper semantics, what you want is for your new types sno and pno to
introduce new values into the type system, as CREATE TYPE does, rather than
being subtypes or aliases of the types they're defined over, as CREATE DOMAIN
does. I believe you can get what you want today with CREATE TYPE pno etc using
an attribute of varchar(50). Two types created in this way, their values should
never compare equal. So then, what you propose above would really just be
syntactic sugar for that. -- Darren Duncan