Типы данных в PostgreSQL

Данные, сохранённые в базе, принадлежат какому-нибудь типу: это может быть целое или вещественное число, строка и т. д.

Тип данных определяет формат записи информации в памяти и доступный диапазон значений. Проще говоря, типы данных влияют на операции, которые этим данным доступны. Числа, которые записаны в базе в виде строк, нельзя сложить, чтобы получить сумму. Поэтому часто приходится менять тип со строкового на числовой и наоборот.

В разных СУБД типы данных могут отличаться, но часто их можно объединить в похожие группы. В PostgreSQL выделяют несколько групп типов данных:

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

Числа и строки встречаются повсеместно. Подсчитывая расходы или набирая текст письма, вы работаете с разными типами данных: числами и строками соответственно.

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

Заказ доставлен покупателю.
С поставщиком товара продолжают сотрудничать.
Работник магазина получил премию.

Такую информацию хранят логические значения. Их ещё называют булевыми в честь математика Джорджа Буля. Таких значений всего два: TRUE, когда утверждение истинно, и FALSE — когда ложно.

В одну группу объединяется несколько типов данных. Например, символьных типов в PostgreSQL несколько: один для небольших строк, другой — для объёмных текстов и т. д.

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

Всего в PostgreSQL выделяют более 40 типов данных, но всё многообразие вам не понадобится. Специалист по запросам редко выбирает типы данных самостоятельно, ведь их определяют ещё во время проектирования базы.

В этом уроке мы расскажем об основных типах данных, с которыми специалисты сталкиваются чаще всего.

Числовые типы

Целые и дробные числа относятся к разным типам данных. Дробные числа иначе называют вещественными.

Тип integer хранит целые числа от -2147483648 до +2147483647. Для простых задач этого хватает. Тип данных integer обозначают либо int, либо int4. Такое обозначение называется псевдонимом. Псевдонимы есть не у всех типов данных, их часто используют по историческим причинам. Да, так действительно написано в документации PostgreSQL.
Тип real обозначает вещественные числа. У этого типа тоже есть псевдоним — float4.

Обратите внимание на число в конце псевдонима — оно обозначает, сколько байт занимает такой тип. К примеру, int4 занимает 4 байта.

Символьные типы

Тип character содержит текст фиксированной длины. Его обозначают псевдонимом char(n). На месте символа n может стоять любое число. Например, если вместо n подставить 4, строка будет состоять только из четырёх символов. Больше нельзя, но меньше можно: недостающие символы будут заполнены пробелами. Если не указывать n, его значение по умолчанию будет 1.
В тип character varying входят тексты нефиксированной длины. Его псевдоним — varchar(n). Значение n управляет лимитом строки, но его можно не указывать.
Тип text содержит строку любой длины. Такого типа в стандарте SQL нет, но в некоторых СУБД, помимо PostgreSQL, его тоже используют.

Поле таблицы базы данных содержит названия самых необычных городов мира. Какие типы данных подойдут для такого поля?

Тип char, только нужно указать длину строки

В таком случае тип char лучше не использовать. Города могут быть совсем короткими, например Э, или подлиннее, как Антананариву.

Тип varchar без ограничения строки

Такой тип данных подойдёт. Город Фюрстенфельдбрук не останется за бортом.

Тип text

Этот тип очень универсальный: в него «поместится» текст любой длины.

Типы для работы с датой и временем

Тип timestamp содержит дату и время. Чаще всего дату и время указывают в формате ISO: 'YYYY-MM-DD'. Например, 30 июня 2010 года записывают так: '2010-06-30'.
В PostgreSQL типов timestamp два: timestamp with time zone, включающий данные о часовом поясе, и timestamp without time zone, который данных о часовом поясе не содержит.
Часовые пояса указывают по стандарту UTC: '2004-10-19 10:23:54+02'. В часовом поясе UTC+2 находится, например, Калининград. Сравните со значением типа timestamp without time zone: '2004-10-19 10:23:54'.
По стандарту SQL обозначение timestamp по умолчанию подразумевает тип timestamp without time zone. Для обозначения типа timestamp with time zone в PostgreSQL используют короткую форму timestamptz.
В тип date входит только дата. Дату можно задавать в любом формате, например, October 19, 2004, 2004-Oct-19 или даже 20041019.
Тип time хранит только время и принимает значения от 00:00:00 до 24:00:00.
Тип interval используют для обозначения интервала между датами. Задать нужный интервал можно с помощью условных обозначений или в свободной форме. Например, запись '1 12:59:10' обозначает 1 день 12 часов 59 минут 10 секунд.

Логические типы

В PostgreSQL к типу boolean относят два значения: TRUE и FALSE. Но эти значения можно указывать по-разному.

Для TRUE список аналогий такой: 'true', 't', 'yes', 'y', 'on', '1'.

Вместо FALSE можно написать: 'false', 'f', 'no', 'n', 'off', '0'.

Управление типом данных

Поменять тип данных можно сразу при выгрузке. При этом тип данных поля в самой таблице не изменится. В таких случаях используют оператор CAST:

SQL
SELECT CAST(поле AS тип данных)
FROM таблица; 

Из таблицы track нужно выгрузить поля track_id и album_id. У этих полей числовой тип данных, но выгрузить их нужно в виде текста. Запрос выглядит так:

SQL
SELECT CAST(track_id AS varchar),
       CAST(album_id AS varchar)
FROM track; 