Client Area

PostgreSQL Command: CREATE TYPE

PostgreSQL Command: CREATE TYPE

PostgreSQL is a powerful open-source relational database management system that offers a wide range of features and functionalities. One of the key commands in PostgreSQL is the CREATE TYPE command, which allows users to define their own custom data types. This article will explore the CREATE TYPE command in detail, discussing its syntax, usage, and examples.

Syntax

The syntax for the CREATE TYPE command in PostgreSQL is as follows:

CREATE TYPE type_name AS (attribute_name data_type, ...);

Here, type_name is the name of the custom data type being created, and attribute_name is the name of the attribute within the data type. The data_type specifies the data type of the attribute.

Usage

The CREATE TYPE command is primarily used to create composite types in PostgreSQL. A composite type is a user-defined data type that can contain multiple attributes. These attributes can be of any built-in or user-defined data type.

By creating custom composite types, users can define structured data that can be used in tables, functions, and other database objects. This allows for more flexibility and organization in data storage and retrieval.

Examples

Let’s look at a few examples to better understand the usage of the CREATE TYPE command.

Example 1: Creating a Simple Composite Type

Suppose we want to create a composite type called person with attributes name and age. We can use the following command:

CREATE TYPE person AS (name text, age integer);

This command creates a new composite type called person with two attributes: name of type text and age of type integer.

Example 2: Using a Composite Type in a Table

Once we have created a composite type, we can use it in tables. For example, let’s create a table called employees with a column of type person:

CREATE TABLE employees (
    id serial PRIMARY KEY,
    employee_info person
);

In this example, the employee_info column is of type person, which is our custom composite type. We can now insert data into this table using the person type.

Example 3: Modifying a Composite Type

If we need to modify a composite type, we can use the ALTER TYPE command. For instance, let’s add a new attribute called email to our person type:

ALTER TYPE person ADD ATTRIBUTE email text;

This command adds a new attribute called email of type text to the existing person type.

Summary

The CREATE TYPE command in PostgreSQL allows users to define their own custom composite types. These types can contain multiple attributes of any built-in or user-defined data type. By using the CREATE TYPE command, users can create structured data that can be used in tables, functions, and other database objects.

If you are interested in learning more about PostgreSQL and its features, consider exploring Server.HK, a leading VPS hosting company that offers reliable and high-performance hosting solutions.