Distinct types

Here is an implementation of distinct types, known from SQL99 and
beyond. They are like domains, except that they don't have defaults or
constraints and they do not allow implicit casting to their base type.
The latter aspect is what makes them distinct types. They are useful to
create more type-safe database schemas, to prevent using generic types
such as text or int for everything and then mixing them in inappropriate
ways. This is something domains are not useful for. Much of the
internals are shared with domains nevertheless. The difference is
really only the casting behavior.

To create a distinct type, just run

CREATE TYPE mystring AS text;

On Fri, 2008-10-31 at 16:03 +0200, Peter Eisentraut wrote:
> Here is an implementation of distinct types, known from SQL99 and
> beyond. They are like domains, except that they don't have defaults or
> constraints and they do not allow implicit casting to their base type.
> The latter aspect is what makes them distinct types. They are useful to
> create more type-safe database schemas, to prevent using generic types
> such as text or int for everything and then mixing them in inappropriate
> ways. This is something domains are not useful for. Much of the
> internals are shared with domains nevertheless. The difference is
> really only the casting behavior.
>
> To create a distinct type, just run
>
> CREATE TYPE mystring AS text;
>

It needs documentation, and I included a quick patch for that (if that's
helpful).

It builds fine for me and appears to do everything as advertised.

I skimmed the code and the relevant parts of the SQL standard, but it
may need further review by someone who knows the type system and the SQL
standard better than I.

Regards,
Jeff Davis

On Fri, 2008-11-07 at 09:11 -0800, David E. Wheeler wrote:
> On Nov 6, 2008, at 11:51 PM, Jeff Davis wrote:
>
> > It needs documentation, and I included a quick patch for that (if
> > that's
> > helpful).
>
> You mis-spelled "cast" as "case".
>

Thanks. Updated diff attached.

Regards,
Jeff Davis

Peter Eisentraut wrote:
> Here is an implementation of distinct types,

I'm withdrawing this patch from the current commit fest for further
work. For the record, I have attached the current patch, including the
documentation work by Jeff Davis.

On Tue, 2008-11-25 at 12:21 +0200, Peter Eisentraut wrote:
> Peter Eisentraut wrote:
> > Here is an implementation of distinct types,
>
> I'm withdrawing this patch from the current commit fest for further
> work. For the record, I have attached the current patch, including the
> documentation work by Jeff Davis.

Shame, this was sorely needed.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Tue, 2008-11-25 at 12:21 +0200, Peter Eisentraut wrote:
>> Peter Eisentraut wrote:
>>> Here is an implementation of distinct types,
>> I'm withdrawing this patch from the current commit fest for further
>> work. For the record, I have attached the current patch, including the
>> documentation work by Jeff Davis.
>
> Shame, this was sorely needed.

I understand, but the work required to make it work properly is too much
under the commit fest spirit right now. In particular, I'm thinking we
should try to devise a clever way to make the CREATE ORDERING facility
that SQL has for user-defined types interface with our more general
operator and operator class mechanisms. This would then also benefit
other sorts of user-defined types. There are also a number of unclear
assumptions about the domain behavior implicitly in the system that will
possibly require a lengthy shaking-out process if we add other sorts of
derived types.

On Nov 28, 2008, at 12:46 PM, Peter Eisentraut wrote:

> I understand, but the work required to make it work properly is too
> much under the commit fest spirit right now. In particular, I'm
> thinking we should try to devise a clever way to make the CREATE
> ORDERING facility that SQL has for user-defined types interface with
> our more general operator and operator class mechanisms. This would
> then also benefit other sorts of user-defined types. There are also
> a number of unclear assumptions about the domain behavior implicitly
> in the system that will possibly require a lengthy shaking-out
> process if we add other sorts of derived types

Speaking of other sorts of derived types: might they include something
just like enums, but sorting on the string values defined for the enum
rather than on the order in which the values were defined in the enum?
I'd use something like that all the time…

Thanks,

David

David E. Wheeler wrote:
>
> Speaking of other sorts of derived types: might they include something
> just like enums, but sorting on the string values defined for the enum
> rather than on the order in which the values were defined in the enum?
> I'd use something like that all the time…
>
>

order by foo_enum::text ...

cheers

andrew

On Nov 28, 2008, at 5:09 PM, Andrew Dunstan wrote:

>> Speaking of other sorts of derived types: might they include
>> something just like enums, but sorting on the string values defined
>> for the enum rather than on the order in which the values were
>> defined in the enum? I'd use something like that all the time…
>
> order by foo_enum::text ...

Ah, I didn't realize that. I guess I'd have to index it on ::text,
too. And then, to use the index in WHERE clauses, I'd further have to
compare to ::text, eh?

Best,

David

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Simon Riggs wrote:
>> Shame, this was sorely needed.

> I understand, but the work required to make it work properly is too much
> under the commit fest spirit right now.

Personally I was wondering exactly why it's "sorely needed". There has
been not one field request for this functionality; in fact I'd never
heard of the feature until Peter popped up with his patch. I assumed he
was simply trying to see if we could tick off another SQL feature
checkbox with a small number of lines of code.

If it's going to take a significant amount of work then I think someone
ought to provide an actual justification why it's worth the work.

regards, tom lane

On Fri, 2008-11-28 at 11:20 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Simon Riggs wrote:
> >> Shame, this was sorely needed.
>
> > I understand, but the work required to make it work properly is too much
> > under the commit fest spirit right now.
>
> Personally I was wondering exactly why it's "sorely needed". There has
> been not one field request for this functionality; in fact I'd never
> heard of the feature until Peter popped up with his patch. I assumed he
> was simply trying to see if we could tick off another SQL feature
> checkbox with a small number of lines of code.
>
> If it's going to take a significant amount of work then I think someone
> ought to provide an actual justification why it's worth the work.

Few thoughts:

* Domains don't work very well in conjunction with arrays.

* Strong typing is preferable in complex applications to avoid errors
like sum(ordinal_column). Most developers use this all the time in their
3GL code but cannot use it in SQL.

* Allows migration of code easier from places that use strange sounding
datatypes that can be mapped easily to existing datatypes.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Fri, 2008-11-28 at 11:20 -0500, Tom Lane wrote:
>> If it's going to take a significant amount of work then I think someone
>> ought to provide an actual justification why it's worth the work.

> Few thoughts:

> * Domains don't work very well in conjunction with arrays.

But distinct types would somehow work better?

> * Strong typing is preferable in complex applications to avoid errors
> like sum(ordinal_column). Most developers use this all the time in their
> 3GL code but cannot use it in SQL.

The problem I see with distinct types is that the typing is *too*
strong --- the datatype has in fact got no usable operations whatever.

> * Allows migration of code easier from places that use strange sounding
> datatypes that can be mapped easily to existing datatypes.

Again, distinct types do *not* provide a "mapping to existing types",
because none of the operations carry along. Domains would be more
nearly what you want for that.

regards, tom lane

On Friday 28 November 2008 18:49:17 Tom Lane wrote:
> > * Strong typing is preferable in complex applications to avoid errors
> > like sum(ordinal_column). Most developers use this all the time in their
> > 3GL code but cannot use it in SQL.
>
> The problem I see with distinct types is that the typing is *too*
> strong --- the datatype has in fact got no usable operations whatever.

You are supposed to define your own. It's a new type after all. You only
borrow the representation from an existing one.

Random example, maybe not the best one: When you create an email type based on
text, you don't really want to carry the || operator along, because email ||
email is not an email (usually). The same applies to substring and pretty
much everything else. Domains are not the best solution if you want type
safety.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Friday 28 November 2008 18:49:17 Tom Lane wrote:
>> The problem I see with distinct types is that the typing is *too*
>> strong --- the datatype has in fact got no usable operations whatever.

> You are supposed to define your own. It's a new type after all. You only
> borrow the representation from an existing one.

And the I/O functions ... and you still need enough access to the type
to write useful operators for it. Which is not an issue too much at the
C-code level but it sure is at SQL level.

So this seems to me to be a nice conceptual idea but it's still not
clear that it works well in practice.

regards, tom lane

>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> If it's going to take a significant amount of work then I think
someone
> ought to provide an actual justification why it's worth the work.

This by itself doesn't justify the effort, but I've worked with
databases which would refuse to allow comparison (including within
JOIN conditions) of values if they were in different domains, and I
miss that. It would occasionally keep people from making dumb
mistakes that wasted time. It would also be nice to be able to
prevent inappropriate use of data, as previously mentioned. I'm
afraid I don't have any interest less mundane than that, and ease of
use would need to be there in order for it to be useful.

I would actually like to see comparisons to literals or expressions of
the base type work, although that doesn't seem to be in line with the
SQL spec, and I suspect it might not play nice with the implicit
casting.

-Kevin

I have a feeling what it would actually take to make this useful might
be to turn every type into a kind of polymorphic type like our anyelem
and anyarray. So substring(mystring) would work and return a mystring
but mystring=string would fail.

--
greg