PostgreSQL — тип данных

В этой главе мы обсудим типы данных, используемые в PostgreSQL. При создании таблицы для каждого столбца вы указываете тип данных, т. Е. Какой тип данных вы хотите хранить в полях таблицы.

Это дает несколько преимуществ —

• Согласованность. Операции со столбцами одного типа данных дают согласованные результаты и обычно являются самыми быстрыми.

• Валидация — правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.

• Компактность — поскольку столбец может хранить значения одного типа, он хранится в компактном виде.

• Производительность — правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.

Согласованность. Операции со столбцами одного типа данных дают согласованные результаты и обычно являются самыми быстрыми.

Валидация — правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.

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

Производительность — правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.

PostgreSQL поддерживает широкий набор типов данных. Кроме того, пользователи могут создавать свои собственные типы данных с помощью команды CREATE TYPE SQL. В PostgreSQL существуют разные категории типов данных. Они обсуждаются ниже.

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

Числовые типы состоят из двухбайтовых, четырехбайтовых и восьмибайтовых целых чисел, четырехбайтовых и восьмибайтовых чисел с плавающей запятой и десятичных дробей с выбираемой точностью. В следующей таблице перечислены доступные типы.

название	Размер хранилища	Описание	Спектр
SMALLINT	2 байта	целое число малого диапазона	От -32768 до +32767
целое число	4 байта	типичный выбор для целого числа	От -2147483648 до +2147483647
BIGINT	8 байт	большое целое число	От -9223372036854775808 до 9223372036854775807
десятичный	переменная	указанная пользователем точность, точная	до 131072 цифр перед десятичной точкой; до 16383 знаков после запятой
числовой	переменная	указанная пользователем точность, точная	до 131072 цифр перед десятичной точкой; до 16383 знаков после запятой
реальный	4 байта	переменная точность, неточная	Точность 6 десятичных цифр
двойная точность	8 байт	переменная точность, неточная	Точность 15 десятичных цифр
smallserial	2 байта	небольшое автоинкрементное целое число	От 1 до 32767
последовательный	4 байта	автоинкрементное целое число	1 до 2147483647
bigserial	8 байт	большое автоинкрементное целое число	1 до 9223372036854775807

Денежные Типы

Тип money хранит сумму в валюте с фиксированной дробной точностью. Значения типов данных numeric, int и bigint могут быть приведены к деньгам . Использование чисел с плавающей точкой не рекомендуется для обработки денег из-за возможной ошибки округления.

название	Размер хранилища	Описание	Спектр
Деньги	8 байт	сумма в валюте	От -92233720368547758.08 до +92233720368547758.07

Типы персонажей

В приведенной ниже таблице перечислены типы символов общего назначения, доступные в PostgreSQL.

С. Нет.	Имя и описание
1	различные символы (n), varchar (n) переменная длина с ограничением
2	символ (n), символ (n) фиксированная длина, с подкладкой
3	текст переменная неограниченная длина

различные символы (n), varchar (n)

переменная длина с ограничением

символ (n), символ (n)

фиксированная длина, с подкладкой

текст

переменная неограниченная длина

Двоичные типы данных

Тип данных bytea позволяет хранить двоичные строки, как показано в таблице ниже.

название	Размер хранилища	Описание
BYTEA	1 или 4 байта плюс фактическая двоичная строка	двоичная строка переменной длины

Типы даты / времени

PostgreSQL поддерживает полный набор типов даты и времени SQL, как показано в таблице ниже. Даты считаются по григорианскому календарю. Здесь все типы имеют разрешение 1 микросекунда / 14 цифр, кроме типа даты , разрешение которого — день .

название	Размер хранилища	Описание	Низкое значение	Высокое значение
отметка времени [(p)] [без часового пояса]	8 байт	дата и время (без часового пояса)	4713 г. до н.э.	294276 н.э.
TIMESTAMPTZ	8 байт	дата и время с часовым поясом	4713 г. до н.э.	294276 н.э.
Дата	4 байта	дата (без времени суток)	4713 г. до н.э.	5874897 н.э.
время [(p)] [без часового пояса]	8 байт	время суток (без даты)	00:00:00	24:00:00
время [(p)] с часовым поясом	12 байт	только время суток, с часовым поясом	00: 00: 00 + 1459	24: 00: 00-1459
интервал [поля] [(p)]	12 байт	интервал времени	-178000000 лет	178000000 лет

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

PostgreSQL предоставляет стандартный тип SQL Boolean. Тип данных Boolean может иметь состояния true , false и третье состояние неизвестно , которое представлено нулевым значением SQL.

название	Размер хранилища	Описание
логический	1 байт	состояние истинного или ложного

Перечислимый тип

Перечислимые (enum) типы — это типы данных, которые содержат статический упорядоченный набор значений. Они эквивалентны типам enum, поддерживаемым во многих языках программирования.

В отличие от других типов, перечисляемые типы необходимо создавать с помощью команды CREATE TYPE. Этот тип используется для хранения статического упорядоченного набора значений. Например, направления по компасу, например, СЕВЕР, ЮГ, ВОСТОК и ЗАПАД или дни недели, как показано ниже —

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

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

Геометрический тип

Геометрические типы данных представляют собой двумерные пространственные объекты. Самый фундаментальный тип, точка, формирует основу для всех других типов.

название	Размер хранилища	Представление	Описание
точка	16 байт	Точка на плоскости	(Х, у)
линия	32 байта	Бесконечная линия (не полностью реализована)	((X1, y1), (х2, у2))
LSEG	32 байта	Конечный отрезок	((X1, y1), (х2, у2))
коробка	32 байта	Прямоугольная коробка	((X1, y1), (х2, у2))
дорожка	16 + 16n байт	Закрытый путь (похож на полигон)	((X1, y1), …)
дорожка	16 + 16n байт	Открытый путь	[(X1, y1), …]
многоугольник	40 + 16n	Полигон (похож на замкнутый путь)	((X1, y1), …)
круг	24 байта	Круг	<(x, y), r> (центральная точка и радиус)

Тип сетевого адреса

PostgreSQL предлагает типы данных для хранения IPv4, IPv6 и MAC-адресов. Лучше использовать эти типы вместо обычных текстовых типов для хранения сетевых адресов, потому что эти типы предлагают проверку ошибок ввода и специализированные операторы и функции.

название	Размер хранилища	Описание
CIDR	7 или 19 байт	Сети IPv4 и IPv6
инет	7 или 19 байт	IPv4 и IPv6 хосты и сети
MacAddr	6 байт	MAC-адреса

Тип битовой строки

Типы битовых строк используются для хранения битовых масок. Это либо 0, либо 1. Существует два типа битов SQL: bit (n) и bitinging (n) , где n — положительное целое число.

Тип текстового поиска

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

С. Нет.	Имя и описание
1	поисковый вектор Это отсортированный список отдельных слов, которые были нормализованы для объединения различных вариантов одного и того же слова, называемые «лексемы».
2	tsquery Это хранит лексемы, которые нужно искать, и объединяет их, соблюдая логические операторы & (AND), | (Или) и! (НЕ). Круглые скобки могут быть использованы для принудительной группировки операторов.

поисковый вектор

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

tsquery

Это хранит лексемы, которые нужно искать, и объединяет их, соблюдая логические операторы & (AND), | (Или) и! (НЕ). Круглые скобки могут быть использованы для принудительной группировки операторов.

Тип UUID

UUID (универсальные уникальные идентификаторы) записывается в виде последовательности шестнадцатеричных цифр в нижнем регистре, в нескольких группах, разделенных дефисами, в частности, в группе из восьми цифр, за которой следуют три группы из четырех цифр, за которыми следует группа из 12 цифр, для всего 32 цифры, представляющие 128 бит.

Пример UUID — 550e8400-e29b-41d4-a716-446655440000

Тип XML

Тип данных XML может использоваться для хранения данных XML. Для хранения данных XML сначала необходимо создать значения XML с помощью функции xmlparse следующим образом:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Тип JSON

Тип данных json может использоваться для хранения данных JSON (JavaScript Object Notation). Такие данные также могут быть сохранены в виде текста , но у типа данных json есть преимущество проверки того, что каждое сохраненное значение является допустимым значением JSON. Также доступны связанные вспомогательные функции, которые можно использовать непосредственно для обработки типа данных JSON следующим образом.

пример	Пример результата
array_to_json ( ‘{{1,5}, {99100}}’ :: Int [])	[[1,5], [99100]]
row_to_json (строка (1, ‘Foo’))	{ «F1»: 1, «F2»: «Foo»}

Тип массива

PostgreSQL дает возможность определять столбец таблицы как многомерный массив переменной длины. Могут быть созданы массивы любого встроенного или определенного пользователем базового типа, типа enum или составного типа.

Декларация массивов

Тип массива может быть объявлен как

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

или используя ключевое слово «ARRAY» как

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Вставка значений

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

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’); 

Доступ к массивам

Пример доступа к массивам приведен ниже. Приведенная ниже команда выберет людей, чьи сбережения больше во втором квартале, чем в четвертом квартале.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Модификация массивов

Пример изменения массивов приведен ниже.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

или используя синтаксис выражения ARRAY —

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Поиск массивов

Пример поиска в массивах приведен ниже.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Если размер массива известен, можно использовать метод поиска, указанный выше. Иначе, следующий пример показывает, как искать, когда размер неизвестен.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Композитные типы

Этот тип представляет список имен полей и их типов данных, т. Е. Структуру строки или записи таблицы.

Декларация составных типов

В следующем примере показано, как объявить составной тип

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

Этот тип данных может использоваться в таблицах создания, как показано ниже:

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Ввод композитного значения

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

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Это действительно для описанного выше инвентаризационного элемента. Ключевое слово ROW на самом деле является необязательным, если у вас есть более одного поля в выражении.

Доступ к составным типам

Чтобы получить доступ к полю составного столбца, используйте точку, за которой следует имя поля, подобно выбору поля из имени таблицы. Например, чтобы выбрать некоторые подполя из нашей таблицы примеров on_hand, запрос будет выглядеть так, как показано ниже —

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Вы даже можете использовать имя таблицы (например, в многопользовательском запросе), например так:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Типы диапазонов

Типы диапазона представляют собой типы данных, которые используют диапазон данных. Типом диапазона могут быть дискретные диапазоны (например, все целочисленные значения от 1 до 10) или непрерывные диапазоны (например, любой момент времени между 10:00 и 11:00).

Доступные встроенные типы диапазонов включают следующие диапазоны —

• int4range — диапазон целого числа

• int8range — диапазон от bigint

• numrange — диапазон числовых значений

• tsrange — диапазон меток времени без часового пояса

• tstzrange — диапазон меток времени с часовым поясом

• диапазон дат — диапазон дат

int4range — диапазон целого числа

int8range — диапазон от bigint

numrange — диапазон числовых значений

tsrange — диапазон меток времени без часового пояса

tstzrange — диапазон меток времени с часовым поясом

диапазон дат — диапазон дат

Пользовательские типы диапазонов могут быть созданы, чтобы сделать доступными новые типы диапазонов, такие как диапазоны IP-адресов, использующие тип inet в качестве базы, или диапазоны с плавающей точкой, использующие тип данных float в качестве базы.

Типы диапазонов поддерживают включающие и исключающие границы диапазона, используя символы [] и () соответственно. Например, «[4,9)» представляет все целые числа, начиная с 4 и включая до 9, но не включая 9.

Типы идентификаторов объектов

Идентификаторы объектов (OID) используются внутри PostgreSQL в качестве первичных ключей для различных системных таблиц. Если указано WITH OIDS или включена переменная конфигурации default_with_oids , только тогда в таких случаях OID добавляются в созданные пользователем таблицы. В следующей таблице перечислены несколько типов псевдонимов. Типы псевдонимов OID не имеют собственных операций, за исключением специализированных подпрограмм ввода и вывода.

название	Рекомендации	Описание	Пример значения
подъязычная	любой	числовой идентификатор объекта	564182
regproc	pg_proc	имя функции	сумма
regprocedure	pg_proc	функция с типами аргументов	сумма (int4)
regoper	pg_operator	имя оператора	+
regoperator	pg_operator	оператор с типами аргументов	* (целое число, целое число) или — (НЕТ, целое число)
regclass	pg_class	имя отношения	pg_type
regtype	pg_type	имя типа данных	целое число
regconfig	pg_ts_config	конфигурация текстового поиска	английский
regdictionary	pg_ts_dict	словарь текстового поиска	просто

Псевдо-типы

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

В приведенной ниже таблице перечислены существующие псевдотипы.

любой

Указывает, что функция принимает любой тип входных данных.

anyelement

Указывает, что функция принимает любой тип данных.

anyarray

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

anynonarray

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

anyenum

Указывает, что функция принимает любой тип данных enum.

anyrange

Указывает, что функция принимает любой тип данных диапазона.

CString

Указывает, что функция принимает или возвращает строку C с нулевым символом в конце.

внутренний

Указывает, что функция принимает или возвращает внутренний тип данных сервера.

language_handler

Обработчик вызова процедурного языка объявляется как возвращающий language_handler.

fdw_handler

Обработчик обёртки сторонних данных объявляется как возвращающий fdw_handler.

запись

Определяет функцию, возвращающую неопределенный тип строки.

спусковой крючок

Объявлена ​​триггерная функция для возврата триггера.

недействительным

Указывает, что функция не возвращает значения.