begriffs open source - ai-pg/blob - full-docs/txt/sql-createdomain.txt

   1
   2 CREATE DOMAIN
   3
   4    CREATE DOMAIN — define a new domain
   5
   6 Synopsis
   7
   8 CREATE DOMAIN name [ AS ] data_type
   9     [ COLLATE collation ]
  10     [ DEFAULT expression ]
  11     [ domain_constraint [ ... ] ]
  12
  13 where domain_constraint is:
  14
  15 [ CONSTRAINT constraint_name ]
  16 { NOT NULL | NULL | CHECK (expression) }
  17
  18 Description
  19
  20    CREATE DOMAIN creates a new domain. A domain is essentially a data type
  21    with optional constraints (restrictions on the allowed set of values).
  22    The user who defines a domain becomes its owner.
  23
  24    If a schema name is given (for example, CREATE DOMAIN myschema.mydomain
  25    ...) then the domain is created in the specified schema. Otherwise it
  26    is created in the current schema. The domain name must be unique among
  27    the types and domains existing in its schema.
  28
  29    Domains are useful for abstracting common constraints on fields into a
  30    single location for maintenance. For example, several tables might
  31    contain email address columns, all requiring the same CHECK constraint
  32    to verify the address syntax. Define a domain rather than setting up
  33    each table's constraint individually.
  34
  35    To be able to create a domain, you must have USAGE privilege on the
  36    underlying type.
  37
  38 Parameters
  39
  40    name
  41           The name (optionally schema-qualified) of a domain to be
  42           created.
  43
  44    data_type
  45           The underlying data type of the domain. This can include array
  46           specifiers.
  47
  48    collation
  49           An optional collation for the domain. If no collation is
  50           specified, the domain has the same collation behavior as its
  51           underlying data type. The underlying type must be collatable if
  52           COLLATE is specified.
  53
  54    DEFAULT expression
  55           The DEFAULT clause specifies a default value for columns of the
  56           domain data type. The value is any variable-free expression (but
  57           subqueries are not allowed). The data type of the default
  58           expression must match the data type of the domain. If no default
  59           value is specified, then the default value is the null value.
  60
  61           The default expression will be used in any insert operation that
  62           does not specify a value for the column. If a default value is
  63           defined for a particular column, it overrides any default
  64           associated with the domain. In turn, the domain default
  65           overrides any default value associated with the underlying data
  66           type.
  67
  68    CONSTRAINT constraint_name
  69           An optional name for a constraint. If not specified, the system
  70           generates a name.
  71
  72    NOT NULL
  73           Values of this domain are prevented from being null (but see
  74           notes below).
  75
  76    NULL
  77           Values of this domain are allowed to be null. This is the
  78           default.
  79
  80           This clause is only intended for compatibility with nonstandard
  81           SQL databases. Its use is discouraged in new applications.
  82
  83    CHECK (expression)
  84           CHECK clauses specify integrity constraints or tests which
  85           values of the domain must satisfy. Each constraint must be an
  86           expression producing a Boolean result. It should use the key
  87           word VALUE to refer to the value being tested. Expressions
  88           evaluating to TRUE or UNKNOWN succeed. If the expression
  89           produces a FALSE result, an error is reported and the value is
  90           not allowed to be converted to the domain type.
  91
  92           Currently, CHECK expressions cannot contain subqueries nor refer
  93           to variables other than VALUE.
  94
  95           When a domain has multiple CHECK constraints, they will be
  96           tested in alphabetical order by name. (PostgreSQL versions
  97           before 9.5 did not honor any particular firing order for CHECK
  98           constraints.)
  99
 100 Notes
 101
 102    Domain constraints, particularly NOT NULL, are checked when converting
 103    a value to the domain type. It is possible for a column that is
 104    nominally of the domain type to read as null despite there being such a
 105    constraint. For example, this can happen in an outer-join query, if the
 106    domain column is on the nullable side of the outer join. A more subtle
 107    example is
 108 INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
 109
 110    The empty scalar sub-SELECT will produce a null value that is
 111    considered to be of the domain type, so no further constraint checking
 112    is applied to it, and the insertion will succeed.
 113
 114    It is very difficult to avoid such problems, because of SQL's general
 115    assumption that a null value is a valid value of every data type. Best
 116    practice therefore is to design a domain's constraints so that a null
 117    value is allowed, and then to apply column NOT NULL constraints to
 118    columns of the domain type as needed, rather than directly to the
 119    domain type.
 120
 121    PostgreSQL assumes that CHECK constraints' conditions are immutable,
 122    that is, they will always give the same result for the same input
 123    value. This assumption is what justifies examining CHECK constraints
 124    only when a value is first converted to be of a domain type, and not at
 125    other times. (This is essentially the same as the treatment of table
 126    CHECK constraints, as described in Section 5.5.1.)
 127
 128    An example of a common way to break this assumption is to reference a
 129    user-defined function in a CHECK expression, and then change the
 130    behavior of that function. PostgreSQL does not disallow that, but it
 131    will not notice if there are stored values of the domain type that now
 132    violate the CHECK constraint. That would cause a subsequent database
 133    dump and restore to fail. The recommended way to handle such a change
 134    is to drop the constraint (using ALTER DOMAIN), adjust the function
 135    definition, and re-add the constraint, thereby rechecking it against
 136    stored data.
 137
 138    It's also good practice to ensure that domain CHECK expressions will
 139    not throw errors.
 140
 141 Examples
 142
 143    This example creates the us_postal_code data type and then uses the
 144    type in a table definition. A regular expression test is used to verify
 145    that the value looks like a valid US postal code:
 146 CREATE DOMAIN us_postal_code AS TEXT
 147 CHECK(
 148    VALUE ~ '^\d{5}$'
 149 OR VALUE ~ '^\d{5}-\d{4}$'
 150 );
 151
 152 CREATE TABLE us_snail_addy (
 153   address_id SERIAL PRIMARY KEY,
 154   street1 TEXT NOT NULL,
 155   street2 TEXT,
 156   street3 TEXT,
 157   city TEXT NOT NULL,
 158   postal us_postal_code NOT NULL
 159 );
 160
 161 Compatibility
 162
 163    The command CREATE DOMAIN conforms to the SQL standard.
 164
 165    The syntax NOT NULL in this command is a PostgreSQL extension. (A
 166    standard-conforming way to write the same for non-composite data types
 167    would be CHECK (VALUE IS NOT NULL). However, per the section called
 168    “Notes”, such constraints are best avoided in practice anyway.) The
 169    NULL “constraint” is a PostgreSQL extension (see also Compatibility).
 170
 171 See Also
 172
 173    ALTER DOMAIN, DROP DOMAIN