СУБД PostgreSQL: Типи даних

PostgreSQL має багатий набір типів даних, доступних для користувачів.

Користувачі можуть додавати нові типи до PostgreSQL за допомогою команди CREATE TYPE.

Визначення користувацького типу даних

CREATE TYPE book_genre AS ENUM (
    'Adventure',
    'Biography',
    'Comedy',
    'Crime',
    'Drama',
    'Fantasy',
    'Historical Fiction',
    'Horror',
    'Mystery',
    'Poetry',
    'Romance',
    'Science Fiction',
    'Self-Help',
    'Thriller',
    'Young Adult'
    );

Назва	Псевдоніми	Опис
`bigint`	`int8`	знакове восьмибайтне ціле число
`bigserial`	`serial8`	автоінкрементоване восьмибайтне ціле число
`bit`		бітовий рядок фіксованої довжини
`bit varying`	`varbit`	бітовий рядок змінної довжини
`boolean`	`bool`	логічний тип даних (true/false)
`box`		прямокутник на площині
`bytea`		двійкові дані («байтовий масив»)
`character`	`char`	символьний рядок фіксованої довжини
`character varying`	`varchar`	символьний рядок змінної довжини
`cidr`		Мережева адреса IPv4 або IPv6
`circle`		коло на площині
`double precision`	`float8`	число з плаваючою комою подвійної точності (8 байт)
`date`		календарна дата (рік, місяць, день)
`inet`		IPv4 або IPv6 адреса хосту
`integer`	`int`, `int4`	знакове чотирибайтне ціле число
`interval`		проміжок часу
`json`		текстові дані JSON
`jsonb`		двійкові дані JSON, декомпоновані
`line`		нескінченна пряма на площині
`lseg`		відрізок прямої на площині
`macaddr`		MAC-адреса (Media Access Control)
`macaddr8`		MAC-адреса (Media Access Control) (формат EUI-64)
`money`		сума валюти
`numeric`	`decimal`	точне число з обраною точністю
`path`		геометричний шлях на площині
`pg_lsn`		Номер послідовності журналу PostgreSQL
`pg_snapshot`		знімок ідентифікатора транзакції на рівні користувача
`point`		геометрична точка на площині
`polygon`		замкнений геометричний шлях на площині
`real`	`float4`	число з плаваючою комою одинарної точності (4 байти)
`smallint`	`int2`	знакове двобайтне ціле число
`smallserial`	`serial2`	автоінкрементоване двобайтне ціле число
`serial`	`serial4`	автоінкрементоване чотирибайтне ціле число
`text`		символьний рядок змінної довжини
`time`		час доби (без часового поясу)
`time with time zone`	`timetz`	час доби, включаючи часовий пояс
`timestamp`		дата і час (без часового поясу)
`timestamp with time zone`	`timestamptz`	дата і час, включаючи часовий пояс
`tsquery`		текстовий пошуковий запит
`tsvector`		текстовий пошуковий документ
`txid_snapshot`		знімок ідентифікатора транзакції на рівні користувача (застарілий; див. pg_snapshot)
`uuid`		універсальний унікальний ідентифікатор
`xml`		XML-дані

Перетворення типів

SQL - це мова сильних типів. Це означає, що кожен елемент даних має асоційований тип даних, який визначає його поведінку та дозволене використання. PostgreSQL має розширювану систему типів, яка є більш загальною і гнучкою, ніж інші реалізації SQL.

Ви можете приводити деякі типи на місці, використовуючи нотацію ::, наприклад:

SELECT '42'::INT;
SELECT '24.5'::REAL;
SELECT 'today'::DATE;
SELECT 'tomorrow'::TIMESTAMP;

Числові типи

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

Типи smallint, integer і bigint зберігають цілі числа, тобто числа без дробової частини, різних діапазонів. Спроби зберігати значення, що виходять за межі допустимого діапазону, призведуть до помилки.

Назва	Розмір	Опис	Діапазон
smallint	2 байти	ціле число малого діапазону	-32768 до +32767
integer	4 байти	типовий вибір для цілого числа	-2147483648 до +2147483647
bigint	8 байт	ціле число великого діапазону	-9223372036854775808 до +9223372036854775807
decimal	змінна	задана користувачем точність, точна	до 131072 знаків до десяткової крапки; до 16383 знаків після десяткової крапки
numeric	змінна	задана користувачем точність, точна	до 131072 знаків до десяткової крапки; до 16383 знаків після десяткової крапки
real	4 байти	змінної точності, неточний	6 десяткових знаків після коми
double precision	8 байт	змінної точності, неточний	точність до 15 знаків після коми
smallserial	2 байти	невелике ціле число, що автоматично збільшується	1 до 32767
serial	4 байти	автоінкрементоване ціле число	1 до 2147483647
bigserial	8 байт	велике ціле число, що автоматично збільшується	1 до 9223372036854775807

Символьні типи

Назва	Опис
`character varying`, `varchar`	змінної довжини з обмеженням
`character`, `char`, `bpchar`	фіксованої довжини, пустотілі
`bpchar` без специфікатора довжини	змінна необмеженої довжини, без обрізів
`text`	змінна необмеженої довжини

У мові SQL визначено два основних типи символів: character varying(n) та character(n), де n - натуральне число. Обидва ці типи можуть зберігати рядок довжиною до n символів. Спроба зберегти довший рядок у стовпці цих типів призведе до помилки, якщо тільки всі надлишкові символи не є пробілами, у цьому випадку рядок буде урізано до максимальної довжини. Однак, якщо явно привести значення до varchar(n) або char(n), то значення надмірної довжини буде урізано до n символів без виникнення помилки. Якщо рядок, що зберігається, коротший за оголошену довжину, значення типу char буде доповнено пробілами; значення типу varchar просто збереже коротший рядок.

Крім того, у postgres передбачено тип text, який зберігає рядки довільної довжини.

Якщо вказано, довжина n повинна бути більшою за 0 і не може перевищувати 10 485 760 символів. Якщо character varying використовується без специфікатора довжини, тип приймає рядки будь-якої довжини. Якщо bpchar не має специфікатора довжини, він також приймає рядки будь-якої довжини, але кінцеві пробіли є семантично несуттєвими. Якщо character не має специфікатора, він еквівалентний до character(1).

Типи дати/часу

PostgreSQL підтримує повний набір типів дати і часу SQL. Дати відраховуються за григоріанським календарем, навіть у роки, коли цей календар ще не було запроваджено.

time, timestamp та interval приймають необов’язкове значення точності, яке визначає кількість дробових знаків, що зберігаються у полі секунд. За замовчуванням, немає явного обмеження на точність. Допустиме значення цього параметра - від 0 до 6.

Тип інтервал має додаткову опцію, яка полягає в обмеженні полів, що зберігаються, шляхом написання однієї з цих фраз:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

Дати

Деякі можливі вхідні дані для типу date.

Приклад	Опис
`1999-01-08`	ISO 8601; 8 січня в будь-якому режимі (рекомендований формат)
`January 8, 1999`	однозначний у будь-якому режимі введення стилю дати
`1/8/1999`	8 січня в режимі MDY; 1 серпня в режимі DMY
`1/18/1999`	18 січня в режимі MDY; відхилено в інших режимах
`01/02/03`	2 січня 2003 року в режимі MDY; 1 лютого 2003 року в режимі DMY; 3 лютого 2001 року в режимі YMD
`1999-Jan-08` `Jan-08-1999` `08-Jan-1999`	8 січня в будь-якому режимі
`99-Jan-08`	8 січня в режимі YMD, інакше помилка
`08-Jan-99` `Jan-08-99`	8 січня, крім помилки в режимі YMD
`19990108` `990108`	ISO 8601; 8 січня 1999 року в будь-якому режимі
`1999.008`	рік та день року
`J2451187`	Дата за юліанським календарем
`January 8, 99 BC`	99 рік до н.е

Час

Деякі можливі вхідні дані для типу time.

Приклад	Опис
`04:05:06.789` `04:05:06` `04:05` `040506`	ISO 8601
`04:05 AM`	те саме, що і 04:05; AM не впливає на значення
`04:05 PM`	збігається з 16:05; вхідна година повинна бути <= 12
`04:05:06.789-8` `04:05:06-08:00` `04:05-08:00` `040506-08`	ISO 8601, з часовим поясом відносно UTC
`040506+0730`	ISO 8601, з часовим поясом у дробових годинах відносно UTC
`040506+07:30:00`	Зміщення UTC вказано в секундах (не дозволено в ISO 8601)
`04:05:06 PST`	часовий пояс, вказаний абревіатурою
`2003-04-12 04:05:06 America/New_York`	часовий пояс вказано повністю

Введення часового поясу

Приклад	Опис
`PST`	Скорочення (для Тихоокеанського стандартного часу)
`America/New_York`	Повна назва часового поясу
`PST8PDT`	Специфікація часових поясів у стилі POSIX
`-8:00:00`	Зміщення відносно UTC для PST
`-8:00`	Зміщення відносно UTC для PST (розширений формат ISO 8601)
`-800`	Зміщення відносно UTC для PST (базовий формат ISO 8601)
`-8`	Зміщення відносно UTC для PST (базовий формат ISO 8601)
`zulu`	Військове скорочення UTC
`z`	Коротка форма зулу (також в ISO 8601)

Спеціальне введення дати/часу

Вхідний Рядок	Допустимі Типи	Опис
epoch	`date`, `timestamp`	1970-01-01 00:00:00+00 (нульовий системний час Unix)
infinity	`date`, `timestamp`	пізніше за всі інші часові мітки
-infinity	date, ``timestamp	раніше за всі інші мітки часу
now	`date`, `time`, `timestamp`	час початку поточної транзакції
today	`date`, `timestamp`	опівночі (00:00) сьогодні
tomorrow	`date`, `timestamp`	опівночі (00:00) завтра
yesterday	`date`, `timestamp`	опівночі (00:00) вчора
allballs	`time`	00:00:00.00 UTC

Інтервали

значення interval можна записати, використовуючи наступний розгорнутий синтаксис:

[@] quantity unit [quantity unit...] [direction]

Інтервали корисні, коли вам потрібно перемістити мітку часу в минуле або в майбутнє.

Абревіатура	Значення
Y	Роки
M	Місяці
W	Тижні
D	Дні
H	Години
M	Хвилини
S	Секунди

7 днів перед вчора

SELECT 'yesterday'::TIMESTAMP + INTERVAL '7d ago';
SELECT 'yesterday'::TIMESTAMP - INTERVAL '7' DAY;

Логічний тип

PostgreSQL надає стандартний тип SQL boolean. Тип boolean може мати декілька станів: true, false і третій стан, unknown, який представляється значенням SQL NULL.

Функція введення типу даних для типу boolean приймає ці представлення рядків для стану true:

true
yes
on
1

і ці представлення для стану false:

false
no
off
0

Ключові слова TRUE або FALSE є найкращим (SQL-сумісним) методом запису булевих констант у SQL-запитах. Але ви також можете використовувати рядкові представлення, дотримуючись загального синтаксису рядково-літерних констант, наприклад, 'yes'::boolean.

Перераховані типи

Перечислювальні типи - це типи даних, які складаються зі статичного, впорядкованого набору значень. Вони еквівалентні типам список, що підтримуються у багатьох мовах програмування. Прикладом перечислення можуть бути дні тижня або набір значень статусу елемента даних.

Типи перечислення створюються за допомогою команди CREATE TYPE; і після створення тип перечислення можна використовувати у таблицях та визначеннях функцій, як і інші типи:

CREATE TYPE WORK_DAY AS ENUM ('mon', 'tue', 'wed', 'thu', 'fri');
CREATE TABLE work_log
(
    amount INTEGER,
    date   DATE,
    day    WORK_DAY
);

Упорядкування

Порядок значень у типі enum - це порядок, у якому значення були перераховані при створенні типу. Усі стандартні оператори порівняння та пов’язані з ними агрегатні функції підтримуються для перелічуваних типів. Однак пам’ятайте, що кожен перелічуваний тип даних є окремим і не може порівнюватися з іншими перелічуваними типами.