How to Create a User-Defined Data Type in PostgreSQL

We always need to define the data types while creating objects in the PostgreSQL database. These are usually built-in data types in PostgreSQL. Sometimes we have to define the data types of our choice. For this purpose, the user-defined data types can be created in two ways. The first approach is by using the CREATE DOMAIN and the second approach used is the CREATE TYPE clause. In this tutorial, we will demonstrate the ways to create user-defined data types. So let’s get started.

How to Create a User-Defined Data Type in PostgreSQL?

The CREATE DOMAIN is used to define user-defined data types with constraints like NOT NULL, CHECK, etc., and the CREATE TYPE is used to create composite type data types which are utilized to determine the structure of complex data objects. It is mostly utilized in a stored procedure as per the data type of the returned value. Let’s discuss both methods one by one.

Using CREATE DOMAIN Clause to Create a User-Defined Data Type in Postgres

We can create a user-defined data type using a CREATE DOMAIN clause. The domain is a data type having a unique name in the schema scope and it contains some constraints like NOT NULL, check, etc. The domain is usually utilized for centralizing the management of some attributes/fields having some common property. Let’s discuss the topic using an example to bring more clarity to it.

Example: Using CREATE DOMAIN Clause to Create a User-Defined Data Type

Consider if we create a table named “merit_scholarship” containing the data for students getting the scholarship. Now if we make two separate columns for name i.e. first name and second name. Both of these fields are not allowed to contain a NULL and a space. The query for this case can be written as:

CREATE TABLE merit_scholarship(
  id SERIAL PRIMARY KEY,
  fname VARCHAR NOT NULL,
  lname VARCHAR NOT NULL,
  scholarship_amount integer,
  CHECK (
  fname !~ '\s'
  AND lname !~ '\s'
  )
   );

In the above query, we have defined all the columns in the table. We have also defined a check constraint that says that the first name i.e. fname and the last name i.e. lname both do not contain spaces. Moreover, where they are defined they say that they can not be NULL. the user can do so by another approach. Instead of a CHECK constraint, we can create a DOMAIN named “student_full_name”, which will do the same thing i.e. it will not contain the NULL and spaces. The query for the domain is written as:

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

The execution of the query will result in the successful creation of DOMAIN which can be ensured by the following output:

We have created a domain instead of the CHECK constraint in the table, we will simply remove the CHECK constraint from the table. The query will become:

CREATE TABLE merit_scholarship(
id SERIAL PRIMARY KEY,
fname student_full_name,
lname student_full_name,
scholarship_amount integer
);

We have defined the data type of the fname and lname fields as domains that we define ourselves. A table will be created as a result of this query like this:

Now we will insert values into the table with the space to see what happens. The query for inserting the values is:

INSERT INTO merit_scholarship(fname, lname,scholarship_amount)
VALUES('Katherine S','Smith', 40085.9);

We can see that the data we want to insert in the fname field contains a space between two letters so the query will definitely throw an error like this:

The error clearly shows that we have violated the conditions defined for the “student_full_name”. Let’s see if it works fine after removing the space:

INSERT INTO merit_scholarship(fname, lname,scholarship_amount)
VALUES('Katherine','Smith', 40085.9)

The output is:

We can see that the values have been successfully inserted into the table if they satisfy the applied checks to the domain. We can see all the domains present in the system by running the “\dD” meta-command in psql. Otherwise, we can simply see all the domains in the side panel of the pgAdmin 4 under the domain options:

This is how we can create User-defined data types using the CREATE DOMAIN clause and by implementing the above method.

We have another method to create user-defined types; let’s see how can we do the same with the other method.

Using CREATE TYPE Clause to Create a User-Defined Data Type in PostgreSQL

The CREATE TYPE creates a composite type and this composite type can be declared as the return type of a function. Let’s take the help of an example to understand the concept clearly.

Example: Using CREATE TYPE Clause to Create a User-Defined Data Type

Let’s suppose we write a function to get the id, name, and scholarship amount for a student, we can use the CREATE TYPE data type. We can first create a user-defined data type using the CREATE TYPE then we will use it in function to return value. The query for the data type creation is:

CREATE TYPE scholarship_details AS (
 Student_id INT,
 Student_name VARCHAR,
 Scholarship_amount DOUBLE PRECISION
 );

The query will create a type. The output will be:

Now if we want to use this data type in the function, we will have to create the function and use it in the function. We can write the query for this as:

CREATE OR REPLACE FUNCTION get_scholarship_details (s_id INT) 
  RETURNS scholarship_details AS 
   $$ 
 SELECT
  Student_id,
  Student_name,
  Scholarship_amount
 FROM
  Students_scholarships
 WHERE
  Student_id = s_id ; 
   $$ 
   LANGUAGE SQL;

In the above function, we have declared the returned type of the function to be the user-defined data type i.e. scholarship_details. The function retrieves the scholarship details for students from the table name “Students_scholarships”.

The execution of this query will ensure the creation of the function by the following output:

Now if we want to get the scholarship details of the student with student ID 2, we will write the following query:

SELECT * FROM get_scholarship_details(2);

We can see the creation of data types by executing the “\dT” in psql and also in the side panel of pgAdmin under the option of “Types” like this:

These were the two methods to create a user-defined data type in PostgreSQL.

Conclusion

Postgres supports the creation of user-defined data types using two different ways. The first way to create a user-defined data type is by utilizing the CREATE DOMAIN statement and the second way is by using the CREATE TYPE commands. In this blog, we have discussed both methods in detail with the help of proper implementation to bring clarity to the topic.

How to Create a User-Defined Data Type in PostgreSQL