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 Указывает, что функция не возвращает значения. |