Create custom data types in PostgreSql

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.

Popular Posts

My most popular posts

Maximum productivity on remote job
Business

Maximum productivity on remote job

I started my own business and intentionally did my best to work from anywhere in the world. Sometimes I sit with my office with a large 27-inch monitor in my apartment in Cheboksary. Sometimes I’m in the office or in some cafe in another city.

Hello! I am Sergey Emelyanov and I am hardworker
Business PHP

Hello! I am Sergey Emelyanov and I am hardworker

I am a programmer. I am an entrepreneur in my heart. I started making money from the age of 11, in the harsh 90s, handing over glassware to a local store and exchanging it for sweets. I earned so much that was enough for various snacks.

Hire Professional CRM developer for $25 per hour

I will make time for your project. Knowledge of Vtiger CRM, SuiteCRM, Laravel, and Vue.js. I offer cooperation options that will help you take advantage of external experience, optimize costs and reduce risks. Full transparency of all stages of work and accounting for time costs. Pay only development working hours after accepting the task. Accept PayPal and Payoneer payment systems. How to hire professional developer? Just fill in the form

Telegram
@sergeyem
Telephone
+4915211100235