PostgreSQL – TYPES

A “type” is a kind of database object that defines a set of possible values and the operations that can be performed on those values. Types can be used to define the structure of columns in tables, arguments and return values of functions, etc.

Here’s an explanation of types as a database object in PostgreSQL:

User-Defined Types

In addition to the built-in data types (like integers, strings, etc.), PostgreSQL allows you to define your own user-defined types using the CREATE TYPE statement. These custom types can have specific attributes and characteristics that you define.

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

Domain Types

A domain type is a user-defined data type that is based on an existing data type but includes additional constraints or validation rules. It allows you to enforce consistency and validation on specific columns.

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

Composite Types

Composite types are user-defined types that can hold multiple fields of various data types. They can be thought of as structures or records.

CREATE TYPE address AS (street text, city text, zip text);

Using Types in Columns

Once you’ve defined a user-defined type, you can use it as the data type for columns in tables

CREATE TABLE person ( id serial PRIMARY KEY, name text, current_mood mood );

Type Casting

PostgreSQL supports type casting, which allows you to convert values from one data type to another explicitly.

SELECT '42'::integer; -- Explicitly cast string to integer

Type Inheritance

PostgreSQL supports type inheritance, which allows you to create a new type that inherits properties from an existing type. This is useful for creating specialized subtypes.

CREATE TYPE student UNDER person (student_id integer);

Built-In Types

PostgreSQL provides a wide range of built-in data types, including numeric, character, binary, date and time, boolean, geometric, network address, and more.

Database objects like types are fundamental to database design and play a crucial role in ensuring data consistency, validation, and usability. By defining and using appropriate types, you can ensure that your data is stored accurately and can be processed efficiently.