PostgreSQL DOMAIN Data Type

• CREATE DOMAIN statement creates a user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint.
• CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.
• If a schema name is given (for example, CREATE DOMAIN myschema.mydomain …) then the domain is created in the specified schema. Otherwise it is created in the current schema. The domain name must be unique among the types and domains existing in its schema.
• Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table’s constraint individually.
• To be able to create a domain, you must have USAGE privilege on the underlying type.

Syntax:

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

Parameters:

When a domain has multiple CHECK constraints, they will be tested in alphabetical order by name. (PostgreSQL versions before 9.5 did not honor any particular firing order for CHECK constraints.)

Notes:

Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));

The empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.

It is very difficult to avoid such problems, because of SQL’s general assumption that a null value is a valid value of every data type. Best practice therefore is to design a domain’s constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.

PRACTICAL1. POSTGRESQL DOMAIN CREATION:

postgres=# CREATE DOMAIN postal_code AS TEXT 
             CHECK(VALUE ~ '^\d{5}$'OR VALUE ~ '^\d{5}-\d{4}$');
CREATE DOMAIN

postgres=# \dD
                                              List of domains
 Schema |    Name     | Type | Modifier |                              Check                               
--------+-------------+------+----------+------------------------------------------------------------------
 public | postal_code | text |          | CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text)
(1 row)
postgres=# CREATE TABLE us_address (address_id SERIAL PRIMARY KEY,
                                     street1 TEXT NOT NULL,
                                     street2 TEXT,
                                     street3 TEXT,
                                     city TEXT NOT NULL,
                                     postal postal_code NOT NULL);

postgres=# \d us_address
                                 Table "public.us_address"
   Column   |    Type     |                            Modifiers                            
------------+-------------+-----------------------------------------------------------------
 address_id | integer     | not null default nextval('us_address_address_id_seq'::regclass)
 street1    | text        | not null
 street2    | text        | 
 street3    | text        | 
 city       | text        | not null
 postal     | postal_code | not null
Indexes:
    "us_address_pkey" PRIMARY KEY, btree (address_id)

Insert some data into “us_address” table:

postgres=# insert into us_address values(1,'add1','add2','add3','cal',76321);
INSERT 0 1

postgres=# insert into us_address values(2,'add1','add2','add3','cal',16321);
INSERT 0 1

postgres=# insert into us_address values(3,'add1','add2','add3','cal',10001);
INSERT 0 1

Domain checking is faster than constraints:
because domain will be check faster than primary key  you will understand from below example

postgres=# insert into us_address values(4,'add1','add2','add3','cal',09001);
ERROR:  value for domain postal_code violates check constraint "postal_code_check"

postgres=# insert into us_address values(3,'add1','add2','add3','cal',09001);
ERROR:  value for domain postal_code violates check constraint "postal_code_check"

postgres=# select * from us_address;
 address_id | street1 | street2 | street3 | city | postal 
------------+---------+---------+---------+------+--------
          1 | add1    | add2    | add3    | cal  | 76321
          2 | add1    | add2    | add3    | cal  | 16321
          3

PRACTICAL 2. CREATION OF DOMAIN WITHOUT CONSTRAINT:
Consider the following scenario on PostgreSQL 9.3:
Step 1. Create a domain with no constraints:

postgres=# CREATE DOMAIN zipcode AS text;
CREATE DOMAIN

Step 2. Add a named constraint:

postgres=# ALTER DOMAIN zipcode ADD CONSTRAINT zipcheck CHECK (char_length(VALUE) = 3);
ALTER DOMAIN

Step 3. Check the Domain whether created or not:

postgres=# \dD        
                                              List of domains
 Schema |    Name     | Type | Modifier |                              Check                               
--------+-------------+------+----------+------------------------------------------------------------------
 public | postal_code | text |          | CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text)
 public | zipcode     | text |          | CHECK (char_length(VALUE) = 3)
(2 rows)

Step 4. Create a table using “zipcode” Data type

postgres=# create table details (name text,code zipcode);
CREATE TABLE

Step 5. Insert some data into “details” table for checking purpose:

postgres=# insert into details values('nijam',123);
INSERT 0 1
postgres=# insert into details values('nijam',676);
INSERT 0 1
postgres=# insert into details values('nijam','abc');
INSERT 0 1
postgres=# insert into details values('nijam','12a');
INSERT 0 1

if You insert below values surely it will throw Errors:

postgres=# insert into details values('nijam',00);  
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',1); 
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',1234);
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',000); 
ERROR:  value for domain zipcode violates check constraint "zipcheck"

postgres=# insert into details values('nijam',000);
ERROR:  value for domain zipcode violates check constraint "zipcheck"

Step 5. Now,suppose that we want to change the constraint using ALTER DOMAIN, for example – drop the domain. The manual says:
Syntax:

ALTER DOMAIN name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ];
postgres=# select conname from pg_constraint where contypid = 'zipcode'::regtype;
 zipcheck

postgres=# ALTER DOMAIN zipcode DROP CONSTRAINT zipcheck ;
ALTER DOMAIN

postgres=# select conname from pg_constraint where contypid = 'zipcode'::regtype;
 conname 
---------
(0 rows)

postgres=# \dD zipcode
              List of domains
 Schema |  Name   | Type | Modifier | Check 
--------+---------+------+----------+-------
 public | zipcode | text |          | 
(1 row)

PRACTICAL 3. POSTGRESQL CREATE DOMAIN NOT NULL DEFAULT ‘N/A’:
Step 1. CREATE DOMAIN statement allows you to create an alias for a built-in data type, and assign range and value constraints:

postgres=# CREATE DOMAIN addrtype VARCHAR(90) NOT NULL DEFAULT 'N/A';
CREATE DOMAIN

postgres=# CREATE DOMAIN idxtype INT CHECK (VALUE > 10 AND VALUE < 99);
CREATE DOMAIN

Step 2. Now we go to create a table as per created domain,note the point “idxtype” having some value in check constraints

postgres=# CREATE TABLE place (name varchar(15),address addrtype,index idxtype); 
CREATE TABLE

Step 3. Insert some data:

postgres=#   INSERT INTO place VALUES('nijam','chennai', 11);
INSERT 0 1

Step 4. Insert default name, address, and index NULL:

postgres=#   INSERT INTO place (index) values (null);          
INSERT 0 1
postgres=# select * from place;
 name  | address | index 
-------+---------+-------
 nijam | chennai |    11
       | N/A     |

Step 5. Insert values some out of range:

postgres=#   INSERT INTO place values ('junaith','kolkatta',9);        
ERROR:  value for domain idxtype violates check constraint "idxtype_check"

PRACTICAL 4. DROPPING DOMAIN:

postgres=# drop domain us_postal_code;
ERROR:  cannot drop type us_postal_code because other objects depend on it
DETAIL:  table us_snail_addy column postal depends on type us_postal_code
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop domain us_postal_code casecade;
ERROR:  syntax error at or near "casecade"
LINE 1: drop domain us_postal_code casecade;
postgres=# drop domain us_postal_code cascade; 
NOTICE:  drop cascades to table us_snail_addy column postal
DROP DOMAIN