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.
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.
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' ) );
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
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, 'firstname.lastname@example.org' );
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:
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.