> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DOMAIN

A user-defined `DOMAIN` data type is based on an existing data type, with optional [constraints](/docs/stable/constraints.md) that restrict its allowed values and an optional [`DEFAULT`](/docs/stable/default-value) value.

## Syntax

To declare a new domain data type, use [`CREATE DOMAIN`](/docs/stable/create-domain):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DOMAIN <name> AS <type> [DEFAULT <expression>] [NOT NULL] [[CONSTRAINT <constraint_name>] CHECK (<expression>)];
```

where `<name>` is the name of the new domain and `<type>` is the base data type. In a `CHECK` expression, the `VALUE` keyword references the value being tested.

You can qualify the `<name>` of a domain with a [database and schema name](/docs/stable/sql-name-resolution.md) (for example, `db.domainname`). After the domain is created, it can only be referenced from the database that contains the domain.

Columns that use a domain data type enforce the domain's constraints on [`INSERT`](/docs/stable/insert), [`UPDATE`](/docs/stable/update), and cast operations. A column inherits the domain's `DEFAULT` value unless the column defines its own.

To view the domains in the current database, use [`SHOW CREATE ALL TYPES`](/docs/stable/show-create):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE ALL TYPES;
```

In the `pg_catalog` system catalog, `pg_type` reports domains with `typtype = 'd'`, and `pg_constraint` includes domain constraints.

To modify a domain, use [`ALTER DOMAIN`](/docs/stable/alter-domain):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN <name> ADD CONSTRAINT <constraint_name> CHECK (<expression>);
```

You can also use `ALTER DOMAIN` to set or drop the default value or `NOT NULL` constraint, rename or drop constraints, rename the domain, set the domain's schema, or change the domain owner's [role specification](/docs/stable/grant.md).

To drop the domain, use [`DROP DOMAIN`](/docs/stable/drop-type#drop-a-domain):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP DOMAIN <name>;
```

## Required privileges

* To [create a domain](/docs/stable/create-domain.md), a user must have the `CREATE` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the parent database and the schema in which the domain is being created.
* To [drop a domain](/docs/stable/drop-type.md#drop-a-domain), a user must be the owner of the domain.
* To [alter a domain](/docs/stable/alter-domain.md), a user must be the owner of the domain.
* To [grant privileges](/docs/stable/grant.md) on a domain, a user must have the `GRANT` privilege and the privilege that they want to grant.
* To create an object that depends on a domain, a user must have the `USAGE` privilege on the domain.

## Examples

### Create and use a domain

Create a domain that requires a 5-digit string value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DOMAIN postal_code AS STRING CHECK (VALUE ~ '^[0-9]{5}$');
```

View the domain definition:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT create_statement FROM [SHOW CREATE ALL TYPES];
```

```
                                            create_statement
---------------------------------------------------------------------------------------------------------
  CREATE DOMAIN public.postal_code AS STRING CONSTRAINT postal_code_check CHECK (value ~ '^[0-9]{5}$');
(1 row)
```

Create a table that uses the domain as a column type:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE addresses (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        street STRING,
        zip postal_code
);
```

Insert a value that satisfies the domain's `CHECK` constraint:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO addresses (street, zip) VALUES ('101 5th Ave', '10003');
```

An `INSERT` that violates the domain's `CHECK` constraint returns an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO addresses (street, zip) VALUES ('200 Main St', 'ABCDE');
```

```
ERROR: value for domain postal_code violates check constraint "postal_code_check"
SQLSTATE: 23514
```

Only the valid row was inserted:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM addresses;
```

```
                   id                  |   street    |  zip
---------------------------------------+-------------+--------
  a54f3016-8fb6-4093-9a82-848fcbbaf016 | 101 5th Ave | 10003
(1 row)
```

The column definition references the domain:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE addresses;
```

```
  table_name |                  create_statement
-------------+-----------------------------------------------------
  addresses  | CREATE TABLE public.addresses (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     street STRING NULL,
             |     zip public.postal_code NULL,
             |     CONSTRAINT addresses_pkey PRIMARY KEY (id ASC)
             | ) WITH (schema_locked = true);
(1 row)
```

### Default value inheritance

A column that uses a domain inherits the domain's `DEFAULT` value. A `DEFAULT` defined on the column takes precedence.

Create a domain with a default value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DOMAIN order_qty AS INT DEFAULT 1 CHECK (VALUE > 0);
```

Create a table where one column defines its own default value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE orders (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        qty order_qty,
        restock_qty order_qty DEFAULT 24
);
```

Insert a row that uses only default values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO orders DEFAULT VALUES;
```

The `qty` column inherits the domain's default value, while `restock_qty` uses the column's default value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT qty, restock_qty FROM orders;
```

```
  qty | restock_qty
------+--------------
  1   | 24
(1 row)
```

## Supported casting and conversion

Values of the base type can be [cast](/docs/stable/data-types.md#data-type-conversions-and-casts) to the domain. The cast validates the value against the domain's constraints:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT '10003'::postal_code;
```

```
  postal_code
---------------
  10003
(1 row)
```

A cast that violates the domain's constraints returns an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT 'ABCDE'::postal_code;
```

```
ERROR: value for domain postal_code violates check constraint "postal_code_check"
SQLSTATE: 23514
```

In expressions, a domain value behaves as a value of its base type.

## Known limitations

* The base type of a domain cannot be an array, composite, or domain data type.
* Domain `DEFAULT` and `CHECK` expressions cannot reference database objects such as tables, sequences, [user-defined functions](/docs/stable/user-defined-functions.md), or user-defined types.
* `ALTER DOMAIN ... VALIDATE CONSTRAINT` is not supported.
* `DROP DOMAIN ... CASCADE` is not supported.
* [`SHOW TYPES`](/docs/stable/show-types) does not list domains.
* The `domains`, `domain_constraints`, `domain_udt_usage`, and `column_domain_usage` tables in [`information_schema`](/docs/stable/information-schema) are not populated.

## See also

* [Data Types](/docs/stable/data-types.md)
* [`SHOW CREATE`](/docs/stable/show-create)
* [`CREATE DOMAIN`](/docs/stable/create-domain)
* [`ALTER DOMAIN`](/docs/stable/alter-domain)
* [`DROP DOMAIN`](/docs/stable/drop-type#drop-a-domain)
* [`CHECK` constraint](/docs/stable/check)
* [`DEFAULT` value constraint](/docs/stable/default-value)
* [`NOT NULL` constraint](/docs/stable/not-null)