DOMAIN

A user-defined DOMAIN data type is based on an existing data type, with optional that restrict its allowed values and an optional value.

Syntax

To declare a new domain data type, use :

> 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 (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 , , 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;

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 <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 . To drop the domain, use :

> DROP DOMAIN <name>;

Required privileges

To , a user must have the CREATE on the parent database and the schema in which the domain is being created.
To , a user must be the owner of the domain.
To , a user must be the owner of the domain.
To 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:

> CREATE DOMAIN postal_code AS STRING CHECK (VALUE ~ '^[0-9]{5}$');

View the domain definition:

> 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:

> 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:

> INSERT INTO addresses (street, zip) VALUES ('101 5th Ave', '10003');

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

> 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:

> SELECT * FROM addresses;

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

The column definition references the domain:

> 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:

> CREATE DOMAIN order_qty AS INT DEFAULT 1 CHECK (VALUE > 0);

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

> 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:

> INSERT INTO orders DEFAULT VALUES;

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

> SELECT qty, restock_qty FROM orders;

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

Supported casting and conversion

Values of the base type can be to the domain. The cast validates the value against the domain’s constraints:

> SELECT '10003'::postal_code;

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

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

> 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, , or user-defined types.
ALTER DOMAIN ... VALIDATE CONSTRAINT is not supported.
DROP DOMAIN ... CASCADE is not supported.
does not list domains.
The domains, domain_constraints, domain_udt_usage, and column_domain_usage tables in are not populated.

Statements

Syntax

Data Types

Spatial Data

Constraints

Syntax

Required privileges

Examples

Create and use a domain

Default value inheritance

Supported casting and conversion

Known limitations

See also

​Syntax

​Required privileges

​Examples

​Create and use a domain

​Default value inheritance

​Supported casting and conversion

​Known limitations

​See also

Syntax

Required privileges

Examples

Create and use a domain

Default value inheritance

Supported casting and conversion

Known limitations

See also