Thread: Implementing DB2's "distinct" types

Hi,

I recently stumbled upon a really cool feature in DB2: distinct types.

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". 

Take the following example:

create type sno as varchar(50)
   with comparisons;

create type pno as varchar(50)
   with comparisons;

create table s
(
    sno    sno         not null primary key,
    .... other columns
);

create table p
(
    pno    pno           not null primary key,
    .... other columns
);

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 wonder if a  similar behaviour can be achieved with Postgres' types as well.

As a type definition in Postgres can also include comparison functions, I have the feeling that this should be
possible,but I don't have an idea on how to start to be honest. 

Any ideas?

Regards
Thomas

Sent from my iPad

On 21-Apr-2013, at 16:47, Thomas Kellerer <spam_eater@gmx.net> wrote:

> Hi,
>
> I recently stumbled upon a really cool feature in DB2: distinct types.
>
> DB2 lets you define your own types (just as Postgres) but with the added benefit that you can mark them such that
theyare _not_ comparable, e.g. to avoid comparing "apples to oranges". 
>
> Take the following example:
>
> create type sno as varchar(50)
>  with comparisons;
>  create type pno as varchar(50)
>  with comparisons;
>
> create table s
> (
>   sno    sno         not null primary key,
>   .... other columns
> );
>
> create table p
> (
>   pno    pno           not null primary key,
>   .... other columns
> );
>
> 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 wonder if a  similar behaviour can be achieved with Postgres' types as well.
>
> As a type definition in Postgres can also include comparison functions, I have the feeling that this should be
possible,but I don't have an idea on how to start to be honest. 
>
> Any ideas?
>
> Regards
> Thomas
>
>
>
>
>
>


Please mark this to pgsql-hackers, where you might get much better constructive feedback.

It does sound nice.

Atri

>> 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 wonder if a  similar behaviour can be achieved with Postgres' types as well.
>>
>> As a type definition in Postgres can also include comparison functions, I have the feeling that
>>this should be possible, but I don't have an idea on how to start to be honest.
>>
>
> Please mark this to pgsql-hackers, where you might get much better constructive feedback.
>
> It does sound nice.

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)

Thomas

Sent from my Thunderbird

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

On 21 April 2013 12:17, Thomas Kellerer <spam_eater@gmx.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.

What does the SQL standard say about this?

Is this actually useful for anything? I don't remember any complaints
about the current behaviour.

> As a type definition in Postgres can also include comparison functions, I
> have the feeling that this should be possible, but I don't have an idea on
> how to start to be honest.

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

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

If you can work out where to put it, you'd just need an if test to
prevent the row comparison recursing into its component types. That
would be stored on the pg_type catalog table as a boolean attribute,
defaulting to current behaviour.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs, 28.04.2013 21:42:
> On 21 April 2013 12:17, Thomas Kellerer <spam_eater@gmx.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
toordering). 


> 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