DOMAIN

DOMAIN is one of the object types in a relational database.A domain is created as a specific data type with attributes attached to it (think of attributes like length, precision or scale, nullability, check constraints).Once a domain has been defined in the database, it can be reused repeatedly to define table columns, PSQL arguments and PSQL local variables.Those objects inherit all attributes of the domain.Some attributes can be overridden when the new object is defined, if required.

This section describes the syntax of statements used to create, alter and drop domains.A detailed description of domains and their usage can be found in Custom Data Types — Domains.

`CREATE DOMAIN`

Creates a new domain

Available in

DSQL, ESQL

Syntax

CREATE DOMAIN name [AS] <datatype>
  [DEFAULT {<literal> | NULL | <context_var>}]
  [NOT NULL] [CHECK (<dom_condition>)]
  [COLLATE collation_name]

<datatype> ::=
  <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::=
  !! See Scalar Data Types Syntax !!

<blob_datatype> ::=
  !! See BLOB Data Types Syntax !!

<array_datatype> ::=
  !! See Array Data Types Syntax !!

<dom_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN ({<val> [, <val> ...] | <select_list>})
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT FROM <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<dom_condition>)
  | NOT <dom_condition>
  | <dom_condition> OR <dom_condition>
  | <dom_condition> AND <dom_condition>

<operator> ::=
    <> | != | ^= | ~= | = | < | > | <= | >=
  | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    VALUE
  | <literal>
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <cast_type>)
  | (<select_one>)
  | func([<val> [, <val> ...]])

<cast_type> ::= <domain_or_non_array_type> | <array_datatype>

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

Table 1. `CREATE DOMAIN` Statement Parameters
Parameter	Description
name	Domain name.The maximum length is 63 characters
datatype	SQL data type
literal	A literal value that is compatible with datatype
context_var	Any context variable whose type is compatible with datatype
dom_condition	Domain condition
collation_name	Name of a collation that is valid for charset_name, if it is supplied with datatype or, otherwise, is valid for the default character set of the database
select_one	A scalar `SELECT` statement — selecting one column and returning only one row
select_list	A `SELECT` statement selecting one column and returning zero or more rows
select_expr	A `SELECT` statement selecting one or more columns and returning zero or more rows
expression	An expression resolving to a value that is compatible with datatype
genname	Sequence (generator) name
func	Internal function or UDF

The CREATE DOMAIN statement creates a new domain.

Any SQL data type can be specified as the domain type.