PostgreSQL - создаём пользовательские типы данных

PostgreSQL поддерживает различные типы данных, которые хранятся в базе и обрабатываются с помощью SQL. Различные типы данных используются в самых различных ситуациях: для оптимального использования типов данных при описании атрибутов таблиц, для обработки данных с помощью стандартных функций, для наложения логических ограничений в соответствии с бизнес-логикой проекта.

Чтобы посмотреть все типы данных, которые поддерживает PostgreSql, нам потребуется команда:

select typname, typlen, typtype from pg_type;

значения typtype:

• b - базовый тип (base)
• c - составной (composite)
• d - для домена (domain)
• e - перечисляемый (enum)
• p - псевдотип (pseudo-type)
• r - диапазон (range)

В данной статье мы не будем рассматривать все типы, отметим только те, которые нам потретуются для дальнейшего создания пользовательских типов.

• Целые числа. Это smallint, integer, bigint. Обычно это данные о ценах, количествах, возрасте. Целочисленные столбцы часто используются для хранения идентификаторов, таких как идентификатор клиента, служащего или номер заказа.
• Десятичные числа. Например, numeric. Имеют дробную часть, но которые вычисляются точно. Если важна точность при хранении и вычислении, рекомендуется использовать именно numeric
• Перечисления. Или Enum. Определяют статический упорядоченный набор значений, так же как и типы enum, существующие в ряде языков программирования. В качестве перечисления можно привести дни недели или набор состояний.

Зачастую, вам может потребоваться создать пользовательский тип данных. Для этого вводим следующие команды:

Составной тип:

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

Тип перечислений:

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

Диапазонный тип:

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

Базовый тип:

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

Пустой тип:

CREATE TYPE имя ;

Для чего могут нам потребоваться пользовательские типы данных? В первую очередь, чтобы гарантировать целостность данных за счёт установления ограничений. Самые простые, ограничения, с которыми встречался каждый - NOT NULL, UNIQUE, PRIMARY KEY. Использование корректных типов для наших столбцов тоже является установлением ограничений. И создание пользовательских типов даёт нам огромные возможности.

Например, вы создаёте CRM систему, которая состоит из множества модулей. И в каждой из записи таблицы вы хотите хранить информацию о том, откуда к вам пришла запись - из CRM, API или Webform. Для этого нам в каждую таблицу модуля потребуется добавить новый столбец с перечислениями (ENUM). И в каждом из них прописывать перечисления, что не очень удобно. Гораздо проще создать отдельный тип - entity_source:

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

Если посмотреть внимательно на запрос, мы создаём новый тип данных и называем его entity_source. И данный тип у нас будет являться enum.

И теперь мы очень просто можем добавить новый столбец в таблицу клиентов:

ALTER TABLE contacts ADD COLUMN source AS entity_source;

После этого мы можем менять значения данного столбца на один из прописанных в enum:

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

И если мы попробуем выполнить запрос, в котором в качестве source задаём несуществующее значение, мы получим ошибку:

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

ERROR: invalid input value

Таким образом, создание пользовательских типов данных могут существенно упростить жизнь как администраторам, так и разработчикам.