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.
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' ) );
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, '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: