PostgreSQL – USER DEFINED Data Types

In PostgreSQL, you have the ability to define your own custom data types, known as user-defined types (UDTs). This allows you to create data structures that suit your specific needs, encapsulate complex logic, and enhance data integrity. User-defined types can be composed of existing built-in data types or even other user-defined types. Here are some key points about user-defined data types:

1. CREATE TYPE Statement: To create a user-defined data type, you use the CREATE TYPE statement. You specify the attributes and behavior of the type, which can include input and output functions, operators, and more.
2. Attributes: A user-defined type can have attributes similar to columns in a table. Each attribute has a name and a data type associated with it.
3. Functions and Operators: You can define functions and operators that work with your user-defined type. This allows you to encapsulate custom logic and behavior.
4. Table Columns: You can use your user-defined type as a data type for columns in tables, just like built-in types.
5. Querying and Manipulating: Once your user-defined type is created, you can query and manipulate it using SQL just like you would with built-in types.
6. Complex Data Structures: You can create complex user-defined types composed of other user-defined types or built-in types. This is especially useful for modeling complex relationships or encapsulating business logic.
7. DROP TYPE Statement: You can use the DROP TYPE statement to remove a user-defined data type when it’s no longer needed.

Here’s an example of creating and using a user-defined data type:

CREATE TYPE point AS ( x double precision, y double precision );

CREATE TABLE coordinates 
( point_id serial PRIMARY KEY, location point ); 

INSERT INTO coordinates (location) 
VALUES (ROW(10.0, 20.0)), (ROW(5.0, 15.0)); 

SELECT * FROM coordinates 
WHERE location.x > 7.0;

In this example, a user-defined data type named point is created to represent 2D coordinates. A table named coordinates is then created with a column of the point type. This demonstrates how you can define and use user-defined types to handle structured data in PostgreSQL.