User-Defined Datatype - (PostgreSQL Data Types)

PostgreSQL provides a wide range of built-in data types for storing various types of data. However, sometimes the built-in types may not meet your requirements, and you may need to define your own data type. In this tutorial, we'll show you how to create user-defined data types in PostgreSQL.

Syntax

CREATE TYPE type_name AS (field1 data_type, field2 data_type, ...);

• type_name: The name of the user-defined data type.
• field1, field2, ...: The fields of the user-defined data type and their corresponding data types.

Example

Let's create a user-defined data type for storing contact information consisting of a name, phone number, and email address.

CREATE TYPE contact_info AS (
  name text,
  phone text,
  email text
);

Now we can use the contact_info data type to create tables that have columns with this type.

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text NOT NULL,
  contact_details contact_info
);

Explanation

In the above example, we created a user-defined data type named contact_info with fields for name, phone number, and email address. We then used the contact_info data type as the data type for a column in a table named users. The users table has columns for id, name, and contact_details.

Use

User-defined data types can be used to simplify the creation and maintenance of complex data structures. They can also make queries and joins easier to write and read.

Important Points

• User-defined data types are created with the CREATE TYPE command.
• User-defined data types can have any number of fields and data types.
• User-defined data types are used to create columns in tables.

Summary

In this tutorial, we discussed how to create user-defined data types in PostgreSQL. We covered the syntax, example, explanation, use, and important points of creating user-defined data types. With this knowledge, you can now create custom data types to meet your specific requirements and simplify the creation and management of complex data structures in PostgreSQL.