PostgreSQL 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.

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.

PostgreSQL User-Defined data type

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:

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.

PostgreSQL User-Defined data type

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:

Output

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

PostgreSQL User-Defined data type

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:
PostgreSQL User-Defined data type

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.

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:

PostgreSQL User-Defined data type

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

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.

PostgreSQL User-Defined data type

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:

Output

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

PostgreSQL User-Defined data type

Step3

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

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:

PostgreSQL User-Defined data type

DROP/ALTER Domain

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

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.

PostgreSQL User-Defined data type

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

Output

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

PostgreSQL User-Defined data type

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:

Output

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

PostgreSQL User-Defined data type

Step2

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

Output

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

PostgreSQL User-Defined data type

Step3

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

Output

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

PostgreSQL User-Defined data type

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.

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.

PostgreSQL User-Defined data type

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

Output

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

PostgreSQL User-Defined data type

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:

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:


PostgreSQL User-Defined data type

Step3

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

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:

PostgreSQL User-Defined data type

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:

PostgreSQL User-Defined data type

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.

Next TopicPostgreSQL Schema




Contact US

Email:[email protected]

User-Defined data type
10/30