PostgreSQL – CREATE DOMAIN

PostgreSQL supports the creation of user-defined data types using the following statements: 

• CREATE DOMAIN:It creates a user-defined data type that can have optional constraints. 
  
• CREATE TYPE:It is often applied to create a composite type (data type that are mixes of two or more data types) using stored procedures. 
  

CREATE DOMAIN:
In PostgreSQL, a domain is a data type that has optional constraints. It is a must to have a unique name and a well-defined schema range. 

Example:
First, we create a table (say, marksheet) using the below command: 

CREATE TABLE marksheet (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    marks_obtained INT NOT NULL, 
    CHECK (
        first_name !~ '\s'
        AND last_name !~ '\s'
    )
);

The first_name and last_name fields are not null and must not contain any spaces. For the simplicity of the teachers we can make a student_detail domain using the below command: 

CREATE DOMAIN student_detail AS 
    VARCHAR NOT NULL CHECK (value !~ '\s');

Now we can use the student_detail as the data type of the first_name and last_name fields as follows: 

CREATE TABLE marksheet (
    student_id serial PRIMARY KEY,
    first_name student_detail,
    last_name student_detail,
    marks_obtained INT NOT NULL,
    email VARCHAR NOT NULL
);

Now insert some data to the table using the below command: 

INSERT INTO marksheet (first_name, last_name,marks_obtained, email)
VALUES
    (
        'Raju K',
        'Singh',
     95,
        'rajukumar@gmail.com'
    );

At this stage PostgreSQL will raise the following error: 

ERROR:  value for domain marksheet violates check constraint "marksheet_check"

So, the behaviour is as we expected. To modify or delete a domain, one can make use of the ALTER DOMAIN or DROP DOMAIN respectively. 
To get all domains in the current database use the below command: 

\dD

Output: