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

# DOMAIN

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

A user-defined `DOMAIN` data type is based on an existing data type, with optional <InternalLink path="constraints">constraints</InternalLink> that restrict its allowed values and an optional <InternalLink path="default-value">`DEFAULT`</InternalLink> value.

## Syntax

To declare a new domain data type, use <InternalLink path="create-domain">`CREATE DOMAIN`</InternalLink>:

```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 <InternalLink path="sql-name-resolution">database and schema name</InternalLink> (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 <InternalLink path="insert">`INSERT`</InternalLink>, <InternalLink path="update">`UPDATE`</InternalLink>, 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 <InternalLink path="show-create">`SHOW CREATE ALL TYPES`</InternalLink>:

```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 <InternalLink path="alter-domain">`ALTER DOMAIN`</InternalLink>:

```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 <InternalLink path="grant">role specification</InternalLink>.

To drop the domain, use <InternalLink path="drop-type#drop-a-domain">`DROP DOMAIN`</InternalLink>:

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

## Required privileges

* To <InternalLink path="create-domain">create a domain</InternalLink>, a user must have the `CREATE` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the parent database and the schema in which the domain is being created.
* To <InternalLink path="drop-type#drop-a-domain">drop a domain</InternalLink>, a user must be the owner of the domain.
* To <InternalLink path="alter-domain">alter a domain</InternalLink>, a user must be the owner of the domain.
* To <InternalLink path="grant">grant privileges</InternalLink> 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 <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> 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, <InternalLink path="user-defined-functions">user-defined functions</InternalLink>, or user-defined types.
* `ALTER DOMAIN ... VALIDATE CONSTRAINT` is not supported.
* `DROP DOMAIN ... CASCADE` is not supported.
* <InternalLink path="show-types">`SHOW TYPES`</InternalLink> does not list domains.
* The `domains`, `domain_constraints`, `domain_udt_usage`, and `column_domain_usage` tables in <InternalLink path="information-schema">`information_schema`</InternalLink> are not populated.

## See also

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="show-create">`SHOW CREATE`</InternalLink>
* <InternalLink path="create-domain">`CREATE DOMAIN`</InternalLink>
* <InternalLink path="alter-domain">`ALTER DOMAIN`</InternalLink>
* <InternalLink path="drop-type#drop-a-domain">`DROP DOMAIN`</InternalLink>
* <InternalLink path="check">`CHECK` constraint</InternalLink>
* <InternalLink path="default-value">`DEFAULT` value constraint</InternalLink>
* <InternalLink path="not-null">`NOT NULL` constraint</InternalLink>