Обсуждение: 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