Create custom data types in PostgreSql

To view all data types that PostgreSql supports, we need the command:

select typname, typlen, typtype from pg_type;

Meanings of datatype:

b - base

c - composite

d - domain

e - enum

p - pseudo-type

r - range

In this article, we will not consider all types, we will mark only those that we will need to further create custom types.

• Whole numbers. They are smallint, integer, bigint. Usually these are data on prices, quantities, age. Integer columns are often used to store identifiers such as customer ID, employee ID, or order number.
• Decimal numbers. For example numeric. They have a fractional part, but which are calculated exactly. If accuracy is important in storage and calculation, it is recommended to use exactly numeric
• Lists. Or Enum. They define a static ordered set of values, just like enum types that exist in a number of programming languages. The days of the week or a set of states can be listed as an enumeration.

Often times, you may need to create a custom data type. To do this, enter the following commands:

Composite:

CREATE TYPE имя AS (имя_атрибута тип_данных [, ... ] );

Enum:

CREATE TYPE имя AS ENUM ( 'метка' [, ... ] );

Range:

CREATE TYPE имя AS RANGE (SUBTYPE = подтип [, ОПЦИИ] );

Base:

CREATE TYPE имя (INPUT = функция_ввода, OUTPUT = функция_вывода [, ОПЦИИ] );

Empty:

CREATE TYPE имя ;

Why do we need custom data types? First of all, to ensure the integrity of the data by establishing restrictions. The simplest constraints that everyone has encountered are NOT NULL, UNIQUE, PRIMARY KEY. Using the correct types for our columns is also a constraint. And creating custom types gives us tremendous opportunities.

For example, you are creating a CRM system that consists of many modules. And in each of the table records, you want to store information about where the record came from - from CRM, API or Webform. To do this, we need to add a new column with enumerations (ENUM) to each table of the module. And in each of them it is not very convenient to write enumerations. It's much easier to create a separate type - entity_source:

CREATE TYPE entity_source AS enum('CRM', 'API', 'Webform');

Looking closely at the request, we create a new datatype and call it entity_source. And this type will be enum.

And now we can very easily add a new column to the customers table:

ALTER TABLE contacts ADD COLUMN source AS entity_source;

After that, we can change the values of this column to one of those registered in the enum:

UPDATE contacts SET source = 'CRM' WHERE id = 1;

And if we try to execute a query in which we set a non-existent value as source, we will get an error:

UPDATE contacts SET source = 'Book' WHERE id = 1;

ERROR: invalid input value

Thus, creating custom data types can greatly simplify the lives of both administrators and developers.