PostgreSQL - User-defined Data Type

In PostgreSQL, users can define their own custom data types. These user-defined data types provide a way to extend the set of built-in data types, allowing you to store and manage data in a way that fits specific needs beyond the standard types PostgreSQL offers. User-defined types can be based on existing types or can be defined from scratch.

Here's a breakdown of how to create and use user-defined data types in PostgreSQL:

Composite Types:

Composite types define a structure consisting of multiple fields, much like a table row. However, instead of being stored as a separate table, they can be used as column data types or function return types.

Syntax:

CREATE TYPE type_name AS (
    field_name1 data_type1,
    field_name2 data_type2,
    ...
);

Example:

CREATE TYPE address_type AS (
    street VARCHAR(100),
    city VARCHAR(50),
    zip_code VARCHAR(10)
);

Enumerated Types:

Enumerated (enum) types are data types consisting of a static, ordered set of values.

Syntax:

CREATE TYPE type_name AS ENUM (
    'value1',
    'value2',
    ...
);

Example:

CREATE TYPE mood AS ENUM (
    'happy',
    'sad',
    'neutral'
);

Domain Types:

Domains are essentially existing base types with constraints. They allow you to define data types with specific validation rules.

Syntax:

CREATE DOMAIN domain_name AS data_type
[ DEFAULT expression ]
[ CONSTRAINT constraint_name constraint_expression ];

Example:

CREATE DOMAIN positive_integer AS INT
CHECK (VALUE > 0);

Array Types:

By default, for each data type in PostgreSQL, there's a corresponding array type available. For instance, for the type INTEGER, there's an array type INTEGER[].

Using the Custom Types:

Once you've defined your custom data types, you can use them in table definitions and other database objects:

CREATE TABLE person (
    name VARCHAR(100),
    address address_type,
    current_mood mood
);

INSERT INTO person (name, address, current_mood)
VALUES ('John Doe', ('123 Main St', 'Sample City', '12345'), 'happy');

Modifying or Removing Custom Types:

Modifying or dropping user-defined types that are in use can be tricky because any dependent objects (like tables or functions) may be affected. To drop a type safely, ensure no objects are using it, or use the CASCADE option:

DROP TYPE IF EXISTS mood CASCADE;

Points to Remember:

Consistency: While user-defined data types provide flexibility, ensure that their usage remains consistent across your database schema to avoid confusion.
Performance: Custom data types can introduce complexity, which might affect performance. It's essential to test and ensure that any user-defined types don't degrade system performance or query efficiency.
Documentation: If you're using custom types extensively, maintaining clear documentation is crucial. Ensure other developers or DBAs working on the database know the purpose and constraints of each custom type.

In summary, PostgreSQL offers a rich system for defining custom data types, providing the flexibility to model data in ways that fit specific application needs. Proper design, consistent usage, and clear documentation are keys to leveraging this feature effectively.

PostgreSQL user-defined data type example: Create a basic user-defined data type:
```
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
```
How to create user-defined data type in PostgreSQL: Define a new user-defined data type:
```
CREATE TYPE color AS ENUM ('red', 'green', 'blue');
```
PostgreSQL composite user-defined data type: Create a composite user-defined data type:
```
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code TEXT
);
```
Modify user-defined data type in PostgreSQL: Alter an existing user-defined data type:
```
ALTER TYPE mood ADD VALUE 'excited';
```
User-defined data type constraints in PostgreSQL: Add constraints to a user-defined data type:
```
CREATE DOMAIN positive_integer AS INTEGER CHECK (VALUE >= 0);
```

PostgreSQL user-defined data type array: Create an array of a user-defined data type:

CREATE TYPE rgb_color AS (
    red INTEGER,
    green INTEGER,
    blue INTEGER
);
CREATE TYPE rgb_color_array AS rgb_color[];

Drop user-defined data type in PostgreSQL: Remove a user-defined data type:
```
DROP TYPE color;
```
Using user-defined data type in a table in PostgreSQL: Utilize a user-defined data type in a table definition:
```
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT,
    mood mood
);
```

PostgreSQL user-defined data type and indexing: Use a user-defined data type in an indexed column:

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price numeric(10,2),
    weight_kg positive_integer
);
CREATE INDEX idx_weight_kg ON product(weight_kg);