CREATE DOMAIN

The CREATE DOMAIN creates a new in a . A domain is based on an existing data type, with optional that restrict its allowed values and an optional value. After the domain is created, it can only be referenced from the database that contains the domain.

The CREATE DOMAIN statement performs a schema change. For more information about how online schema changes work in CockroachDB, refer to .

Synopsis

Parameters

Parameter	Description
`type_name`	The name of the domain. You can qualify the name with a (for example, `db.domainname`), but after the domain is created, it can only be referenced from the database that contains the domain.
`typename`	The base of the domain.
`DEFAULT a_expr`	The default value for columns that use the domain data type. A column inherits this value unless the column defines its own `DEFAULT` value.
`NOT NULL`	Disallow `NULL` values for the domain.
`NULL`	Allow `NULL` values for the domain. This is the default behavior; the clause is accepted for PostgreSQL compatibility.
`CONSTRAINT name`	The name of a `CHECK` constraint. If not specified, CockroachDB generates a name (for example, `order_qty_check` for a domain named `order_qty`).
`CHECK (a_expr)`	A expression that values of the domain must satisfy. Use the `VALUE` keyword to reference the value being tested. A domain can have multiple `CHECK` constraints.

Required privileges

To create a domain, the user must have on the parent database and the schema in which the domain is being created. To use a domain in a table (for example, when defining a column’s type), the user must have on the domain.

Details

Columns that use a domain data type enforce the domain’s constraints on , , and cast operations.
Domain DEFAULT and CHECK expressions cannot reference database objects such as tables, sequences, , or user-defined types. A domain can be based on a user-defined type such as an .
The base type of a domain cannot be an array, composite, or domain data type.

Examples

Create a domain with a default value and constraints

The following statement creates a domain based on DECIMAL that defaults to 0, disallows NULL values, and rejects negative values:

> CREATE DOMAIN price AS DECIMAL DEFAULT 0 NOT NULL CHECK (VALUE >= 0);

Create a domain with a named constraint

The following statement creates a domain with a CHECK constraint named us_phone_format:

> CREATE DOMAIN us_phone AS STRING CONSTRAINT us_phone_format CHECK (VALUE ~ '^[0-9]{10}$');

To view the domains in the current database, use :

> SELECT create_statement FROM [SHOW CREATE ALL TYPES];

                                                create_statement
-----------------------------------------------------------------------------------------------------------------
  CREATE DOMAIN public.price AS DECIMAL DEFAULT 0:::DECIMAL NOT NULL CONSTRAINT price_check CHECK (value >= 0);
  CREATE DOMAIN public.us_phone AS STRING CONSTRAINT us_phone_format CHECK (value ~ '^[0-9]{10}$');
(2 rows)

Use a domain in a table

Use the domains created in the preceding examples as column types:

> CREATE TABLE products (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING,
        list_price price,
        support_line us_phone
);

The list_price column inherits the domain’s DEFAULT value:

> INSERT INTO products (name, support_line) VALUES ('widget', '8005550100');

> SELECT name, list_price, support_line FROM products;

   name  | list_price | support_line
---------+------------+---------------
  widget | 0          | 8005550100
(1 row)

A value that violates the price_check constraint returns an error:

> INSERT INTO products (name, list_price) VALUES ('gadget', -1.99);

ERROR: value for domain price violates check constraint "price_check"
SQLSTATE: 23514

The error message includes the constraint name that was specified at creation:

> INSERT INTO products (name, support_line) VALUES ('gizmo', '555-0100');

ERROR: value for domain us_phone violates check constraint "us_phone_format"
SQLSTATE: 23514

Known limitations

Refer to .

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Parameters

Required privileges

Details

Examples

Create a domain with a default value and constraints

Create a domain with a named constraint

Use a domain in a table

Known limitations

See also

​Synopsis

​Parameters

​Required privileges

​Details

​Examples

​Create a domain with a default value and constraints

​Create a domain with a named constraint

​Use a domain in a table

​Known limitations

​See also

Synopsis

Parameters

Required privileges

Details

Examples

Create a domain with a default value and constraints

Create a domain with a named constraint

Use a domain in a table

Known limitations

See also