PostgreSQL - Тип данных

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

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

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

  • Validation - Правильное использование типов данных подразумевает проверку формата данных и отклонение данных, выходящих за рамки типа данных.

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

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

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

character varying(n), varchar(n)

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

2

character(n), char(n)

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

3

text

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

Типы двоичных данных

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

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

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

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

имя Размер хранилища Описание Низкое значение Высокое значение
отметка времени [(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 может иметь состояния true , false и третье состояние, unknown , которое представлено нулевым значением SQL.

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

Нумерованный тип

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

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

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

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

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

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

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

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

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

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

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

Типы битовых строк используются для хранения битовых масок. Они либо 0, либо 1. Есть два типа битов SQL:bit(n) и bit varying(n), где n - натуральное число.

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

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

С. Нет. Имя и описание
1

tsvector

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

2

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). Такие данные также могут быть сохранены как текст , но тип данных json имеет то преимущество, что проверяет, является ли каждое сохраненное значение допустимым значением JSON. Доступны также связанные функции поддержки, которые можно использовать непосредственно для обработки типа данных JSON следующим образом.

пример Пример результата
array_to_json ('{{1,5}, {99,100}}' :: int []) [[1,5], [99,100]]
row_to_json (строка (1, 'foo')) {"f1": 1, "f2": "foo"}

Тип массива

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

Объявление массивов

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

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);

Это действительно для указанного выше inventory_item . Ключевое слово 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 - Диапазон метки времени с часовым поясом

  • daterange - Диапазон дат

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

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

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

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

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

Псевдотипы

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

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

С. Нет. Имя и описание
1

any

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

2

anyelement

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

3

anyarray

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

4

anynonarray

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

5

anyenum

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

6

anyrange

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

7

cstring

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

8

internal

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

9

language_handler

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

10

fdw_handler

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

11

record

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

12

trigger

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

13

void

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