Re: Implementing DB2's "distinct" types

Simon Riggs, 28.04.2013 21:42:
> On 21 April 2013 12:17, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>
>> DB2 lets you define your own types (just as Postgres) but with the added
>> benefit that you can mark them such that they are _not_ comparable, e.g. to
>> avoid comparing "apples to oranges".
>
> Sounds like an interesting feature we might want, but you should
> discuss it on hackers.

As I said, I don't really need this as a new feature. I was just curious.

> What does the SQL standard say about this?

C.J. Date calls this "domain constrained comparison".

There is the definition of "distinct type" in the standard (including a "EQUALS ONLY" option, but that seems to relate to ordering).

> Is this actually useful for anything?

I think it's just as useful as restricting the comparison between any other type (e.g. int vs. varchar).

> Postgres supports both domains and row types. So you can treat this as
> a row type with just one attribute.

Two different row types with just one varchar attribute are still comparable:

The following returns one row:

create type apple as (apid varchar(10));
create type orange as (orid varchar(10));

with apples (app) as (
values (cast(row('one') as apple))
), oranges (org) as (
values (cast(row('one') as orange))
)
select a.*
from apples a
join oranges o on a.app = o.org;

> Look at make_row_comparison_op() in src/backend/parser/parse_expr.c

As I said: I wonder if this could be done with pure SQL, rather than creating a C function.
But apparently this does not seem to be the case.

Cheers
Thomas