У каждого столбца в PostgreSQL фиксированный тип, который задаётся в CREATE TABLE. Большинству таблиц хватает нескольких: bigint или integer для целых чисел, numeric для денег, text для строк, boolean, timestamptz для моментов времени и jsonb для документов. pg_typeof() показывает тип любого значения.
Основные типы и когда их использовать
| Тип | Что хранит | Для чего |
|---|---|---|
integer (int) | Целые числа примерно до 2,1 миллиарда, 4 байта | Счётчики, количества, небольшие id |
bigint | Целые числа примерно до 9,2 квинтиллиона, 8 байт | Первичные ключи и всё, что может расти |
smallint | До 32767, 2 байта | Редко оправдан |
numeric(p, s) | Точные десятичные числа | Деньги, ставки, всё, что суммируется |
real, double precision | Двоичные числа с плавающей точкой | Измерения, научные значения |
text | Строки любой длины | Имена, email, описания |
varchar(n) | Строки с ограничением длины | Когда ограничение и есть правило |
boolean | true, false или NULL | Флаги |
date | Календарный день | Дни рождения, сроки |
timestamptz | Момент времени | Время создания и изменения |
interval | Длительность | '2 hours', '30 days' |
uuid | 128-битный идентификатор | Публичные или распределённые id |
jsonb | Разобранный JSON | Данные без фиксированной структуры |
text[], int[] | Массивы любого типа | Короткие списки, теги |
bytea | Сырые байты | Небольшие двоичные значения |
Для самых важных решений есть отдельные страницы: timestamp или timestamptz, типы enum, jsonb и массивы. Идентификаторы (serial, identity-столбцы, uuid) разобраны в разделе о первичных ключах.
numeric или double precision
numeric хранит десятичные числа точно. real и double precision хранят двоичные дроби, поэтому значения вроде 0.1 приблизительны, а суммы могут «уплывать»:
Сумма numeric равна 0.3. Сумма с плавающей точкой равна 0.30000000000000004, а проверка на равенство даёт false. Для денег используйте numeric: numeric(10, 2) допускает 10 цифр всего, из них 2 после точки, и округляет лишние знаки при вставке.
Деление целого числа на целое отбрасывает дробную часть:
7 / 2 возвращает 3. Сделайте одну из сторон numeric (литерал с десятичной точкой или приведение типа), чтобы получить 3.5.
text, varchar(n) и char(n)
Все три хранятся одинаково, и ни один не быстрее. varchar(n) отклоняет более длинные значения при вставке, и это полезно, только если ограничение действительно правило ваших данных. char(n) дополняет значение пробелами, и в новых таблицах ему нет применения. Явное приведение к varchar(n) обрезает значение, а не выдаёт ошибку.
Приведение возвращает Post. Значение char(5) занимает 5 байт, но length не учитывает дополнение и возвращает 2.
Значения boolean и NULL
Столбец boolean принимает true, false, 't', 'yes', 'on', '1' и их противоположности и всегда возвращает true или false. Отсутствующее значение это NULL, которое не является ни тем, ни другим, поэтому WHERE NOT active пропускает строки, где active равно NULL.
Используйте IS TRUE, IS NOT TRUE или COALESCE(active, false), когда NULL должен считаться ложью.
Часто задаваемые вопросы
Есть ли разница в скорости между text и varchar в PostgreSQL?
Нет. text, varchar и varchar(n) хранятся одинаково. varchar(n) лишь добавляет проверку длины, поэтому используйте text, если ограничение не является настоящим правилом ваших данных.
Стоит ли использовать тип money в PostgreSQL?
Обычно нет. Его ввод и вывод зависят от настройки lc_monetary, а значение не хранит валюту. Храните суммы в numeric(12,2), а валюту в отдельном столбце.
Что в PostgreSQL вместо DATETIME?
timestamptz (timestamp with time zone) для моментов времени или timestamp для времени «по часам на стене» без часового пояса. Типа DATETIME в PostgreSQL нет.
Как узнать тип значения или столбца в PostgreSQL?
SELECT pg_typeof(expression) возвращает тип любого выражения, в том числе столбца. В psql команда \d table_name выводит каждый столбец с его типом.