PostgreSQL – CREATE DOMAIN
Last Updated :
06 Sep, 2021
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:
Share your thoughts in the comments
Please Login to comment...