Re: Is CREATE TYPE an alias for CREATE DOMAIN?

Hi

Could somebody please tell me if CREATE TYPE is equivalent to CREATE
DOMAIN? If not is there a work around

---

Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access

John Dean wrote:
> Hi
>
> Could somebody please tell me if CREATE TYPE is equivalent to CREATE
> DOMAIN? If not is there a work around

What do you mean by "equivalent"? You wouldn't use them in the same way,
and I'm not sure what a work-around would consist of.

What are you trying to do?

--
Richard Huxton
Archonet Ltd

On Thu, Dec 22, 2005 at 09:36:52AM +0000, Richard Huxton wrote:
> John Dean wrote:
> >Hi
> >
> >Could somebody please tell me if CREATE TYPE is equivalent to CREATE
> >DOMAIN? If not is there a work around
>
> What do you mean by "equivalent"? You wouldn't use them in the same way,
> and I'm not sure what a work-around would consist of.
>
> What are you trying to do?

Some (most?) database's idea of 'creating a type' is actually what we
consider creating a domain, since many databases don't support users
adding arbitrary types to the system. I suspect this user is trying to
port some code over...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 09:36:52AM +0000, Richard Huxton wrote:
> > John Dean wrote:
> > >Hi
> > >
> > >Could somebody please tell me if CREATE TYPE is equivalent to CREATE
> > >DOMAIN? If not is there a work around
> >
> > What do you mean by "equivalent"? You wouldn't use them in the same way,
> > and I'm not sure what a work-around would consist of.
> >
> > What are you trying to do?
>
> Some (most?) database's idea of 'creating a type' is actually what we
> consider creating a domain, since many databases don't support users
> adding arbitrary types to the system. I suspect this user is trying to
> port some code over...

CREATE DOMAIN builds on an existing data type and adds additional
characteristics and checks to the type. It is sort of like a macro for
types.

CREATE TYPE creates a new data type, independent of existing data types,
and usually requires C code and a shared object file to load into the
database.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Jim C. Nasby wrote:
> Some (most?) database's idea of 'creating a type' is actually what we
> consider creating a domain,

Which databases do such a thing?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Thu, Dec 22, 2005 at 05:16:16PM +0100, Peter Eisentraut wrote:
> Jim C. Nasby wrote:
> > Some (most?) database's idea of 'creating a type' is actually what we
> > consider creating a domain,
>
> Which databases do such a thing?

IIRC, Oracle, DB2, Sybase and MSSQL, though my memory's rusty... and I
should have mentioned that most are just creating an alias for a type
name, so you can't add stuff like constraints to the new type.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Hi Jim

I have checked through the docs for:-
1. Interbase/Firebird
2. Sybase/MS SQL Server
3. Oracle
4. DB2
5. PostgreSQL
BTW I didn't bother to check the MySQL docs because I do not consider MySQL
to be a RDBMS

It seems that only Interbase/Firebird and PostgreSQL supports the CREATE
DOMAIN syntax. DB2 includes something similar - CREATE DISTINCTIVE TYPE.
But it doesn't allow a constraint to be included

At 17:09 28/12/2005, you wrote:
>On Thu, Dec 22, 2005 at 05:16:16PM +0100, Peter Eisentraut wrote:
> > Jim C. Nasby wrote:
> > > Some (most?) database's idea of 'creating a type' is actually what we
> > > consider creating a domain,
> >
> > Which databases do such a thing?
>
>IIRC, Oracle, DB2, Sybase and MSSQL, though my memory's rusty... and I
>should have mentioned that most are just creating an alias for a type
>name, so you can't add stuff like constraints to the new type.

Those RDBMS which do support the CREATE DOMAIN syntax allows the inclusion
of a named constraint and/or a CHECK constrain
Below is a copy of the first few lines from the PostgreSQL SQL Language
Reference

CREATE DOMAIN

Name

CREATE DOMAIN -- define a new domain

Synopsis

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

Description

CREATE DOMAIN creates a new data domain. The user who defines a domain
becomes its owner.

If a schema name is given (for example, CREATE DOMAIN myschema.mydomain
...) then the domain is created in the specified schema. Otherwise it is
created in the current schema. The domain name must be unique among the
types and domains existing in its schema.

Domains are useful for abstracting common fields between tables into a
single location for maintenance. For example, an email address column may
be used in several tables, all with the same properties. Define a domain
and use that rather than setting up each table's constraints individually.

Sybase/MS SQL Server makes use of the store procedure - sp_addtype, which
is similar to DB2's CREATE DISTINCTIVE TYPE
Oracle uses a variation on the CREATE TYPE syntax. But just like Sybase, MS
SQL Server and DB2 it does not accept a named constraint or CHECK clause

>--
>Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
>Pervasive Software http://pervasive.com work: 512-231-6117
>vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

---

Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access