create_domain.sgml 6.56 KiB
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v 1.5 2002/04/23 02:07:15 tgl Exp $
PostgreSQL documentation
-->
<refentry id="SQL-CREATEDOMAIN">
<refmeta>
<refentrytitle id="sql-createdomian-title">
CREATE DOMAIN
</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>
CREATE DOMAIN
</refname>
<refpurpose>
define a new domain
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>2002-02-24</date>
</refsynopsisdivinfo>
<synopsis>
CREATE DOMAIN <replaceable class="parameter">domainname</replaceable> [AS] <replaceable class="parameter">data_type</replaceable>
[ DEFAULT <replaceable>default_expr</> ]
[ <replaceable class="PARAMETER">constraint</replaceable> [, ... ] ]
where <replaceable class="PARAMETER">constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL | NULL }
</synopsis>
<refsect2 id="R2-SQL-CREATEDOMAIN-1">
<refsect2info>
<date>2002-02-24</date>
</refsect2info>
<title>
Parameters
</title>
<para>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">domainname</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of a domain to be created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">data_type</replaceable></term>
<listitem>
<para>
The underlying data type of the domain. This may include array
specifiers.
Refer to the <citetitle>User's Guide</citetitle> for further
information about data types and arrays.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>DEFAULT
<replaceable>default_expr</replaceable></literal></term>
<listitem> <para>
The <literal>DEFAULT</> clause specifies a default value for
columns of the domain data type. The value
is any variable-free expression (but subselects are not allowed).
The
data type of the default expression must match the data type of the
domain.
</para>
<para>
The default expression will be used in any insert operation that
does not specify a value for the column. If there is no default
for a domain, then the default is NULL.
</para>
<note>
<para>
If a default value is specified for a particular column, it
overrides any default associated with the domain. In turn,
the domain default overrides any default value associated with
the underlying data type.
</para>
</note>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
<listitem>
<para>
An optional name for a constraint. If not specified,
the system generates a name.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>NOT NULL</></term>
<listitem>
<para>
Values of this domain are not allowed to be NULL. This is
equivalent to the column constraint <literal>CHECK (<replaceable
class="PARAMETER">column</replaceable> NOT NULL)</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>NULL</></term>
<listitem>
<para>
Values of this domain are allowed to be NULL. This is the default.
</para>
<para>
This clause is only available for compatibility with
non-standard SQL databases. Its use is discouraged in new
applications.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
<refsect2 id="R2-SQL-CREATEDOMAIN-2">
<refsect2info>
<date>2002-02-24</date>
</refsect2info> <title>
Outputs
</title>
<para>
<variablelist>
<varlistentry>
<term><computeroutput>
CREATE DOMAIN
</computeroutput></term>
<listitem>
<para>
Message returned if the domain is successfully created.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
</refsynopsisdiv>
<refsect1 id="R1-SQL-CREATEDOMAIN-1">
<refsect1info>
<date>2002-02-24</date>
</refsect1info>
<title>
Description
</title>
<para>
<command>CREATE DOMAIN</command> allows the user to register a new
data domain with <productname>PostgreSQL</> for use in the
current data base. The user who defines a domain becomes its owner.
</para>
<para>
If a schema name is given (for example, <literal>CREATE DOMAIN
myschema.mydomain ...</>) then the domain is created in the
specified schema. Otherwise it is created in the current schema (the one
at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
The domain name must be unique among the types and domains existing
in its schema.
</para>
<para>
Domains are useful for abstracting common fields between tables into
a single location for maintenance. 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.
</para>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
This example creates the <type>country_code</type> data type and then uses the
type in a table definition:
<programlisting>
CREATE DOMAIN country_code char(2) NOT NULL;
CREATE TABLE countrylist (id INT4, country country_code);
</programlisting>
</para>
</refsect1>
<refsect1 id="SQL-CREATEDOMAIN-compatibility">
<title>Compatibility</title>
<para>
SQL99 defines CREATE DOMAIN, but says that the only allowed constraint
type is CHECK constraints. CHECK constraints for domains are not yet supported by <productname>PostgreSQL</productname>.
</para>
</refsect1>
<refsect1 id="SQL-CREATEDOMAIN-see-also">
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-dropdomain"></member>
<member><citetitle>PostgreSQL Programmer's Guide</citetitle></member>
</simplelist>
</refsect1>
</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->