~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

creates a new domain\&. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values)\&. The user who defines a domain becomes its owner\&.

.PP

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\&.

.PP

Domains are useful for abstracting common constraints on fields into a single location for maintenance\&. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax\&. Define a domain rather than setting up each table\(aqs constraint individually\&.

.SH "PARAMETERS"

.PP

\fIname\fR

.RS 4

The name (optionally schema\-qualified) of a domain to be created\&.

.RE

.PP

\fIdata_type\fR

.RS 4

The underlying data type of the domain\&. This can include array specifiers\&.

.RE

.PP

\fIcollation\fR

.RS 4

An optional collation for the domain\&. If no collation is specified, the underlying data type\(aqs default collation is used\&. The underlying type must be collatable if

COLLATE

is specified\&.

.RE

.PP

DEFAULT \fIexpression\fR

.RS 4

The

DEFAULT

clause specifies a default value for columns of the domain data type\&. The value is any variable\-free expression (but subqueries are not allowed)\&. The data type of the default expression must match the data type of the domain\&. If no default value is specified, then the default value is the null value\&.

.sp

The default expression will be used in any insert operation that does not specify a value for the column\&. If a default value is defined 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\&.

.RE

.PP

CONSTRAINT \fIconstraint_name\fR

.RS 4

An optional name for a constraint\&. If not specified, the system generates a name\&.

.RE

.PP

NOT NULL

.RS 4

Values of this domain are normally prevented from being null\&. However, it is still possible for a domain with this constraint to take a null value if it is assigned a matching domain type that has become null, e\&.g\&. via a LEFT OUTER JOIN, or

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false))\&.

.RE

.PP

NULL

.RS 4

Values of this domain are allowed to be null\&. This is the default\&.

.sp

This clause is only intended for compatibility with nonstandard SQL databases\&. Its use is discouraged in new applications\&.

.RE

.PP

CHECK (\fIexpression\fR)

.RS 4

CHECK

clauses specify integrity constraints or tests which values of the domain must satisfy\&. Each constraint must be an expression producing a Boolean result\&. It should use the key word

VALUE

to refer to the value being tested\&.

.sp

Currently,

100

CHECK

101

expressions cannot contain subqueries nor refer to variables other than

102

VALUE\&.

103

.RE

104

.SH "EXAMPLES"

105

.PP

106

This example creates the

107

us_postal_code

108

data type and then uses the type in a table definition\&. A regular expression test is used to verify that the value looks like a valid US postal code:

109

.sp

110

.if n \{\

111

.RS 4

112

.\}

113

.nf

114

CREATE DOMAIN us_postal_code AS TEXT

115

CHECK(

116

VALUE ~ \(aq^\e\ed{5}$\(aq

117

OR VALUE ~ \(aq^\e\ed{5}\-\e\ed{4}$\(aq

118

);

119

120

CREATE TABLE us_snail_addy (

121

address_id SERIAL PRIMARY KEY,

122

street1 TEXT NOT NULL,

123

street2 TEXT,

124

street3 TEXT,

125

city TEXT NOT NULL,

126

postal us_postal_code NOT NULL

127

);

128

.fi

129

.if n \{\

130

.RE

131

.\}

132

.SH "COMPATIBILITY"

133

.PP

134

The command

135

CREATE DOMAIN

136

conforms to the SQL standard\&.

137

.SH "SEE ALSO"

138

ALTER DOMAIN (\fBALTER_DOMAIN\fR(7)), DROP DOMAIN (\fBDROP_DOMAIN\fR(7))

Older »