PostgreSQL · January 2, 2024

PostgreSQL Command: CREATE DOMAIN

PostgreSQL Command: CREATE DOMAIN

PostgreSQL is a powerful open-source relational database management system that offers a wide range of features and functionalities. One of the key features of PostgreSQL is the ability to create custom data types using the CREATE DOMAIN command. In this article, we will explore the CREATE DOMAIN command and its usage in PostgreSQL.

What is a Domain?

In PostgreSQL, a domain is a user-defined data type that allows you to define constraints and rules on a specific data type. It provides a way to enforce data integrity and consistency by restricting the values that can be stored in a column.

For example, let’s say you have a table called “employees” with a column called “age”. Instead of using the built-in integer data type for the “age” column, you can create a domain called “employee_age” that restricts the age to be between 18 and 65 years. This ensures that only valid age values are stored in the “age” column.

Creating a Domain

The syntax for creating a domain in PostgreSQL is as follows:

CREATE DOMAIN domain_name AS data_type
   [DEFAULT default_value]
   [constraint1]
   [constraint2]
   ...
   [constraint_n];

Let’s break down the syntax:

  • domain_name: The name of the domain you want to create.
  • data_type: The base data type of the domain.
  • DEFAULT default_value (optional): Specifies a default value for the domain.
  • constraint1, constraint2, ..., constraint_n (optional): Specifies constraints on the domain.

Here’s an example of creating a domain called “employee_age” with the integer data type and a check constraint:

CREATE DOMAIN employee_age AS integer
   CHECK (VALUE BETWEEN 18 AND 65);

In this example, the “employee_age” domain restricts the values to be integers between 18 and 65.

Using a Domain

Once you have created a domain, you can use it when defining columns in a table. Here’s an example:

CREATE TABLE employees (
   id serial PRIMARY KEY,
   name varchar(100),
   age employee_age
);

In this example, the “age” column of the “employees” table is defined using the “employee_age” domain. This ensures that only valid age values are stored in the “age” column.

Summary

The CREATE DOMAIN command in PostgreSQL allows you to create custom data types with constraints and rules. Domains provide a way to enforce data integrity and consistency in your database. By using domains, you can define specific rules for your data types and ensure that only valid values are stored in your tables.

If you are looking for a reliable VPS hosting provider in the Hong Kong, consider Server.HK. With top-notch VPS solutions and excellent customer support, Server.HK is the perfect choice for your hosting needs.

You may also like...