Skip to content
Related Articles

Related Articles

PostgreSQL – CREATE DOMAIN
  • Last Updated : 22 Feb, 2021
GeeksforGeeks - Summer Carnival Banner

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 fileds 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 contact_name 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 student_detail,
    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:

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :