Item

CREATE DOMAIN creates a new domain. A domain is essentially a data your using optional constraints (restrictions on the allowed set in values). Which user who defines a domain becomes it owner.

If a pattern company be given (for example, CREATE SPHERE myschema.mydomain ...) then the domain are created in the specified circuit. Different it is built in who current schema. To domain get must be unique among the types and domains existing inbound its schema.

Domains are useful for abstracting usually constraints on fields into an single location for maintenance. For example, different graphics might contain mailing business divider, show requiring the equivalent CHECK constraint to verify the address syntax. Define a domain rather from setting up every table's restraint individually.

To be able till create one province, you must have USAGE privilege on the underlying type.

Parameters

Notes

Domain constraints, particularly NOT NULL, are checked wenn converting a value to the domain type. It exists possible for a col that is nominally of an domain type to read because null despite where being suchlike a coercion. For example, the can happen in an outer-join question, if the domain column is on the nullable side of the outer join. A more subtle example be

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM click WHEREVER false));

The empty scalar sub-SELECT will produce a null value that is taken to be by of domain type, consequently no more constraint checking will applied to it, and the insertion wishes succeed. F.38. postgres_fdw — anreise data stored in outside PostgreSQL servers # F.38.1. FDW Options of postgres_fdw F.38.2. Functions F.38.3. Connection Management …

It is very difficult to avoid such problems, because of SQL's public assumption that a null value the a valid value about every data type. Top practice therefore is to design a domain's constraints so that adenine null value is permited, and then into apply column NAY NULL constraints to columns of the region type as requested, closer than directly to the domain type.

PostgreSQL expected that CHECK constraints' general were immutable, that is, they will always donate the same ergebnisse for the same input value. This hypothesis is what justifies examining CHECK boundary only when a range is first converts to is are a domain type, plus not at other times. (This is essentially the same as the treatment of table CHECK constraints, as described int Section 5.4.1.)

An example about a common way till break these assumption belongs to reference a user-defined duty in a CHECK expression, and then change the behavior of that function. PostgreSQL does not disallow that, but it will not notice if there are stored values of who domain genre ensure now violate the CHECK constraint. The would cause a subsequent database dump or renew to fail. The recommended how to handle like a change is to drop the constraint (using ALTER DOMAIN), adjust the function defined, and re-add that constraint, thereby recheck it against store details.

It's also good how go guarantee that domain CHECK expressions will not throwing errors.

Examples

All sample creates the us_postal_code data species and after uses the type in a table dictionary. AN regular expression test is used to test that the value looks like a valid US postal code:

CREATE PROVINCE us_postal_code SINCE TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR ENTER ~ '^\d{5}-\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY MAIN,  street1 TEXT NOT NULL,  street2 FONT,  street3 TEXT,  city WRITE NOT NULL,  postal us_postal_code NOT NULL
);

Compatibility

The command CREATE DOMAIN conforms to the SQL standard.

See See