User-Defined data type

In this section, we are going to understand the working of the PostgreSQL user-defined data type with the help of CREATE DOMAIN and CREATE TYPE command and see an example of it.

What is PostgreSQL user-defined Data Type?

In addition to built-in data types, PostgreSQL provides us to generate user-defined data types with the help of the below commands:

Commands	Description
CREATE DOMAIN	It generates a user-defined data type with constraints, For Example, CHECK, NOT NULL, etc.
CREATE TYPE	The create type command is used to generate a composite type, which is used in stored procedures as per the data types of returned values.

Now, let us see how the CREATE DOMAIN and CREATE TYPE commands work for PostgreSQL user-defined data.

PostgreSQL CREATE DOMAIN Command

In PostgreSQL, a domain has a unique name within the schema scope as Domain is a data type with elective constraints such as CHECK, NOT NULL. For consolidating the organization of fields with common constraints, domains are always beneficial.

For Example, suppose we have some tables containing a similar column, which does not take NULL and spaces.

In other words, we can say that some of the tables might contain the text columns, which need a CHECK constraint to assure the values are not null and remain expanded with spaces.

Example of Domain data type

Let us see one sample example to understand how the Domain data type works.

We are creating one new table as Recipients with the CREATE command’s help and inserting some values using the INSERT command.

To create Recipients into an Organization database, we use the CREATE command.

The Recipients table contains the following columns, such as Recipient_id, First_name, Last_name and Email.

CREATE TABLE Recipients (  

Recipient_ID SERIAL PRIMARY KEY,  

    First_name VARCHAR NOT NULL,  

    Last_name VARCHAR NOT NULL,  

    Email VARCHAR NOT NULL,  

    CHECK (  

      First_name !~ '\s'  

      AND Last_name !~ '\s'  

    )  

);

Output

We will get the following message on executing the above command, which displays that the Recipients table has been created successfully into the Organization database.

We have used the CHECK constraint in the above table that the First_name and the Last_name columns do not receive spaces and null values.

So, here we can create as person_name Domain and reuse it in various columns to define the CHECK constraint.

In the below command, we are using the CREATE DOMAIN command for generating a new domain called person_name using the VARCHAR data type which do not take the spaces and NULL values:

 CREATE DOMAIN person_name AS   

 VARCHAR NOT NULL CHECK (value!~ '\s'); 

Output

After executing the above command, we will get the below message window, displaying that the person_name domain has been created successfully for the Recipients table.

After creating the domain, we can use the person_name domain as the datatype of the First_name and Last_name columns as a consistent built-in type, as we can see in the following command:

 CREATE TABLE Recipients1 (  

Recipient_ID SERIAL PRIMARY KEY,  

    First_name person_name,  

    Last_name person_name,  

    Email VARCHAR NOT NULL  

    ); 

Output

After executing the above command, we will get the below message window, which displays that the Recipients1 table has been created successfully.

Note:

• Make sure that both the table must have the different name once we create the domain because it takes the same name of the table (Recipients).
• PostgreSQL raises the following error relation “recipients” already exists, as shown in the below screenshot:

Once the Recipients1 table has been created successfully, we will insert some values into it using the INSERT command.

In the below command, we are inserting some value into the Recipients1 table.

 INSERT INTO Recipients (First_name, Last_name, Email)  

VALUES('Mike',' W Ross','[email protected]'); 

Output

After implementing the above command, PostgreSQL raise the following error new row for relation “recipients” violates check constraint “recipients_check” because the last_name column contains the space as shown in the below screenshot:

To resolve the above error, we will provide the Last_name without giving any space, as shown in the following command:

INSERT INTO Recipients (First_name, Last_name, Email)  

VALUES('Mike','Ross','[email protected]'); 

Output

After successfully executing the above command, we will get the below message window, which displays that the particular value has been inserted into the Recipients table.

View Domain in psql

We are going to follow the below process to view a domain in psql:

Step1

Firstly, we will open the psql in our local system, and we will connect to the database where we want to create a table.

Step2

For connecting an Organization database, we will enter the below command:

postgres=# \c Organization   

Output

After executing the above command, we will get the following output:

Step3

Now, we will enter the below command to view a domain in the Organization database.

Organization=# \dD  

Output

On implementing the above command, we will get the below output, which displays the existing domain, which is person_name present in the Organization database:

DROP/ALTER Domain

We can use the DROP DOMAIN or ALTER DOMAIN commands individually for deleting or modifying a domain.

DROP DOMAIN IF EXISTS person_name;  

Output

After executing the above command, we will get the below error message: we cannot drop type person_name because other objects depend on it.

So, in the above command, we are using the CASCADE as it is used to drop the objects automatically, which depend on the table.

DROP DOMAIN IF EXISTS person_name CASCADE;  

Output

After implementing the above command, we will get the following result, displaying that the person_name domain has been dropped successfully.

PostgreSQL CREATE TYPE Command

The PostgreSQL CREATE TYPE command provides us to make a composite type that can be used as the return type of a function.

For example: If we want to have a function, which returns several values: item _id, item_name, and item_price

Step1

Firstly, we will create a type such as Item_details, as we can see in the following command:

CREATE TYPE Item_details AS (  

    item_id INT,  

    item_name VARCHAR,  

    item_price Numeric(5,2)  

); 

Output

After implementing the above command, we will get the following message window, which shows that the item_details type has been created successfully.

Step2

After that, we will use the item_details data type as the return type of a function, as shown in the following command:

CREATE OR REPLACE FUNCTION get_Item_details (I_id INT)   

    RETURNS Item_details AS   

$$   

SELECT item_id, item_name,  item_price   

FROM items  

WHERE item_id = I_id ;   

$$   

LANGUAGE SQL;  

Output

We will get the following message window after executing the above command, which displays that the function has been created successfully.

Step3

At last, we will call the get_Item_details() function with the help of the SELECT command:

SELECT * FROM get_Item_details(3);  

Output

On implementing the above command, we will get the following output, which displays the particular id item, which is Audi A7.

DROP/ALTER TYPE

Like we use the Drop domain command to delete the Domain, we can use the DROP TYPE command for deleting the user-defined data type, and we can use the ALTER TYPE commands for modifying the user-defined data type.

DROP TYPE IF EXISTS Item_details;  

Output

We will get a similar error as above (drop domain) that we cannot drop type Item_details because other objects depend on it on executing the above command.

To resolve this above error, we are using the CASCADE as it is used to drop the objects automatically, which depend on the table.

DROP TYPE IF EXISTS Item_details CASCADE;  

Output

After executing the above command, we will get the below message window, which displays that the specified type has been dropped successfully.

View TYPE in psql

If we are using the psql program, we can list all user-defined types in the existing database with the help of the following commands:

\dT or \dT+  

For Example, we are going to follow the below process to view a type in psql:

Step1

Firstly, we will open the psql in our local system, and we will connect to the database where we want to create a table.

Step2

For connecting an Organization database, we will enter the below command:

postgres=# \c Organization   

Step3

Now, we will enter the below command to view a domain in the Organization database.

Organization=# \dT  

Output

On implementing the above command, we will get the below output, which displays the existing TYPE, which is Item_details present in the Organization database:

Organization=# \dT+  

Output

We will get the below output after executing the above command, which displays the existing TYPE, which is Item_details present in the Organization database:

Overview

In the PostgreSQL user-defined data type section to the most useful operations, we have learned the following topics:

• The PostgreSQL user-defined data type is used to generate user-defined data types with the help of Create DOMAIN and CREATE TYPE
• The CREATE DOMAIN command is used to generate a user-defined data type with constraints such as CHECK, NOT NULL, etc.
• And the CREATE TYPE command is used to generate a composite type, which is used in stored procedures as per the data types of returned values.
• To view the newly created DOMAIN in PSQL, we have used the \dD
• We have used the \dT or \dT+ commands to view the newly generated TYPE in PSQL.
• We have used the DROP DOMAIN command for deleting the user-defined data type, and we can use the ALTER DOMAIN command for modifying the user-defined data type.
• And we have also used the DROP TYPE or ALTER TYPE commands to remove and delete the user-defined type.