SQL-Ex blog

Это хорошее время, чтобы следить за процессом изучения PostgreSQL и моей способности перенести этот процесс обучения на вас. Поймите, что хотя я действительно только изучаю PostgreSQL, у меня большой опыт и знания в области реляционных структур данных. Я начинал с Paradox еще в 1980-е до профессиональной работы с Sybase, Oracle, Access и SQL Server. Поэтому, хотя я изучаю PostgreSQL с нуля, мне не нужно изучать с нуля множество концепций. Раз это так, я могу упустить информацию, которая была бы жизненно важна для вас, если это ваша первая реляционная СУБД.

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

Один момент я бы хотел отметить, и это справедливо для каждой отдельной платформы данных, с которыми я работал, вам действительно следует использовать типы данных по их назначению, а не пытаться прикручивать их к тому, для чего они не подходят. Что я имею в виду? Я имею в виду, что строка - это строка, и она отлична от даты, которая отлична от числа. Можете вы заменить одно другим? Да. Следует ли? Нет. Придерживайтесь использования соответствующего типа данных. Это важно в PostgreSQL, так же как и повсюду.

Теперь давайте начнем.

Обзор типов данных PostgreSQL

Числовые типы			Типы поиска текста

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

Символьные типы			Типы JSON 

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

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

Булевы типы			Типы диапазонов

Перечисляемые типы		Типы идентификаторов объектов

Геометрические типы		Типы доменов

Типы сетевых адресов		Тип Pg_lsn

Типы битовых строк		Псевдо-типы

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

Числовые типы весьма просты. Имеется три целочисленных типа для целых чисел: smallint, integer и bigint. Два точных десятичных типа: numeric и decimal. И типы с плавающей точкой: real и double precision, которые соответствуют математическим структурам с одинарной и двойной точностью. Наконец, имеется набор числовых типов данных, называемых serial (последовательный), которые в действительности не являются типами данных. Это просто автоматически инкрементируемые целые числа, но настроенные так, чтобы вам было легко их использовать при описании таблицы. Они базируются на использовании имеющейся в PostgreSQL операции SEQUENCE (последовательность).

Хочу сразу объяснить, что, несмотря на то, что это автоматически инкрементируемые столбцы, вы можете и, вероятно, увидите зазоры.

За исключением типа serial, числовые типы данных довольно просты.

Денежные типы

Ну, это заблуждение. Имеется фактически один тип money. Тут интересен тот момент, что десятичная часть в money фиксирована, но вы можете ее изменить. Это параметр уровня базы данных, который вы можете настроить в пределах заданного диапазона.

Символьные типы

Здесь я ожидал получить очень длинный список, а он закончился на трех: char, varchar и text. Тип данных char имеет фиксированную длину с дополнением. Это означает, что если вы определяете столбец как pettype char(50) и поместите туда значение cat, будет добавлено 47 пробелов. Тип данных varchar аналогичен переменному символьному типу в других системах баз данных. Столбец pettype varchar(50) и значение cat означает, что будет сохраняться только cat без дополнения. Тип данных varchar имеет переменную длину в отличие от строк фиксированной длины char (другое название для character). Можно также опустить размер типа данных varchar. В этом случае он может содержать любое число символов вплоть до 1 Гб.

Тип text предназначен для неограниченных данных до 1 Гб. Он имеет ряд недостатков, как и аналогичный тип данных в SQL Server.

Самое интересное, что нет отдельного типа данных Unicode. Вместо этого каждый из этих трех типов данных поддерживает Unicode. Вот так. Легко. Честно, мне это нравится. Я всегда считал, что VARCHAR/NVARCHAR в SQL Server доставляют неудобства.

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

Здесь тоже есть только один тип данных bytea. Кажется, я вижу, к чему это идет. Некоторые из «типов» будут просто «тип». Кто-то решил, что определять их таким способом лучше, чем просто относить их к "Другие", как тип, заполненный разнообразным типами данных.

Так или иначе, двоичная строка называется bytea. Существуют некоторые правила и поведение в отношении escape-символов, но в основном он предназначены для хранения двоичных файлов, таких как изображения, в вашей базе данных.

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

Мне нравятся стандарты именования в PostgreSQL. Они делаю вещи значительно более проще по сравнению с другими системами, с которыми я работал (SQL Server). Тип данных timestamp хранит как дату, так и время. Тип данных date хранит даты, но не время. Тип данных time хранит время, но не даты. Наконец, есть тип данных interval. Тип данных interval хранит интервал времени.

Я мог бы до конца статьи толковать о типе данных interval. Он замечательный. Вы можете определить точный интервал от микросекунд до тысячелетий. Вы можете также добавить направление, включив ago на входе, эффективно создавая отрицательный интервал.

Имеется большое число конфигурационных настроек для управления часовым поясом, специальными значениями и другим поведением. Есть также специальные функции даты и времени, а также математические операции с датой/временем.

Это один из тех типов данных, над которым часто измываются. Люди хотят иметь конкретный формат для своих дат. Вместо того, чтобы зафиксировать его на уровне сервера или приложения, они сохраняют даты в виде строк, что является большой ошибкой; это лишает их значительной функциональности. Используйте эти типы данных по их назначению.

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

Имеется один логический тип, Bool. Это включено или выключено. Это 1 или 0. И все.

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

Тоже интересно. Имеется только один перечисляемый тип, или есть бесконечное число перечисляемых типов, в зависимости от точки зрения. По сути, это enum, нумерованный список. Давайте определим один:

CREATE TYPE pets AS ENUM ('cat', 'dog', 'horse', 'cockroach');

CREATE TABLE mypets

(mypet pets)

Вы можете определить тип данных как заданный список. В этом случае только те значения, которые составляют список enum pets, могут быть вставлены в столбец mypet.

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

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

Геометрические типы составляют еще одну интересную категорию. Их имеется несколько, от point (точка) до line segment (линейный сегмент), circle (окружность) и совсем замечательного, пути. У вас имеется большой выбор. Также есть несколько геометрическх функций, который работают с этими типами данных. Сюда мало что можно добавить. Хотя есть только двумерные типы. Все типы выстроены в системе координат X/Y.

Типы сетевых адресов

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

Честно, это круто. И это то, что вы получаете от программного обеспечения с открытым исходным кодом. Конечно, вы могли бы, вероятно, встроить правила и пользовательские типы данных в, скажем, базу данных SQL Server для их эмуляции. Однако для этого нужно поработать. Тут могут помочь также пакеты ПО. Вместо этого имеем встроенный прямо в базу данных довольно стандартный набор потребностей компьютерщиков.

Вы получаете три типа:

• inet: это для адресов хостов IPv4 и IPv6 с дополнительной маской сети.


• cidr: спецификация сетей IPv4 и IPv6.


• macaddr: хранит MAC-адреса, например карт ethernet и других.

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

Типы битовых строк

Еще один интересная категория. Имеется два типа - bit(n) и bit varying(n). Подобно обычным строкам, char или varchar, вы либо получаете дополненный столбец фиксированной длины, либо переменной, но ограниченной длины. Назначение этого типа данных - хранить и/или визуализировать битовые маски. Я не уверен, что много бы работал с ними, но хорошо знать его возможности.

Типы поиска текста

Это интересно. Есть тип данных text, упомянутый ранее как один из символьных типов. Однако очевидно, что вы не можете приложить возможности полнотекстового поиска к столбцу этого типа . Вместо этого вам нужно использовать один из двух типов поиска текста: tsvector и tsquery.

Эти понятия быстро усложняются.

tsvector состоит из списка лексем (в основном хранимых слов, но варианты одного того же слова сливаются в одно). Возьмем, например, такую фразу:

‘I do not like green eggs and ham. I do not like them Sam-I-Am’

Это будет сохранено так:

‘and’ ‘do’ ‘eggs’ ‘green’ ‘ham’ ‘I’ ‘like’ ‘not’ ‘Sam-I-Am’ ‘them’

Видно, что второй набор ‘I do not like’ был исключен, и слова упорядочены в алфавитном порядке. Это делается на заднем плане. Ну, и это становится более сложным. Есть также способы определения местоположения в пределах строк для поиска близости. Вы можете также обновлять ваши лексемы для придания им веса для изменения того, что выводить из полнотекстовых запросов, а что убрать. Затем вы переходите к функциям для работы с поиском.

Говоря о поиске, tsquery — это место, где они хранятся. Это хранит лексемы, подобные приведенным выше, и затем группирует их вместе с использованием логических операторов $ (AND), | (OR) и ! (NOT). Вы можете также управлять логикой с помощью скобок.

Я мог бы долго спускаться в эту кроличью нору. Но пока оставлю это, поскольку тут есть еще много чего.

Тип UUID

Универсальные уникальные идентификаторы (UUID), которые являются тем же, что и глобальные уникальные идентификаторы (GUID). Интересно, что система баз данных сама не генерирует UUID, а опирается на внешние модули для этого.

Типы JSON

Что? Не YAML?

Есть два типа json и jsonb. Между ними есть два ключевых различия, и оба на стороне jsonb. Jsonb хранится в двоичном виде, а не в виде строки, что замедляет запись и извлечение, но делает значительно более быстрой обработку поиска в JSON. Что касается поиска, то jsonb также поддерживает индексы.

Рекомендации в документации говорят, что jsonb будет лучшим выбором для большинства случаев использования. Тип данных json сохраняет все форматирование и не позволяет какое-либо переупорядочение ключей в пределах документа JSON. Зачастую я понимаю, почему для большинства людей все это не имеет значения.

Хранилище jsonb работает также с примитивными типами данных в пределах документов JSON, поэтому вы можете быть уверены, что text - это текст, numeric, как вы знаете, числа, bool — это логические значения, а значение null — это не null, а (none), потому что null означает в SQL что-то совсем другое.

Помимо индексирования, jsonb также имеет возможность определять содержимое, когда один документ JSON сохраняется в другом.

Имеется также масса функций, поддерживающих работу с JSON.

Типы массива

Документация просто говорит "Arrays" (массивы), без сочетания со словом "Type" (тип). Я его возвращаю здесь.

Честно говоря, я бы рассматривал это как эквивалент типа данных VARIANT в SQL Server. Короче, яд.

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

Документация говорит: "Синтаксис CREATE TABLE допускает задание точного размера массивов...". После чего следует: "Однако текущая реализация игнорирует любые указанные пределы размера массива..."

Если вы помните, я говорил, что типы сетей служили примером преимуществ, которые вы можете получить от программного обеспечения с открытыми кодами. Тут я бы сказал, что эта часть функциональности является примером наказания, которое вы можете получить от программного обеспечения с открытым исходным кодом. Я понял. Мы хотим сохранять все везде в любое время любым способом, поскольку это делает вещи "проще". Это хорошо до тех пор, пока вы не поддерживаете это хозяйство, настраиваете производительность, обеспечиваете согласованность данных, наводите порядок после плохо дисциплинированных разработчиков, которые помещают массив любого желаемого размера с любым содержимым, и нет почти никаких средств, чтобы остановить их.

Послушайте. Не делайте этого.

Примеры использования массивов, которые я продолжаю находить в Интернете: "Привет, вы знаете, что можно сделать вместо нормализации ваших структур данных..." Ага, смотрите производительность. Имеются обобщенные инвертированные индексы (GIN). Они позволяют индексировать значения таким образом, чтобы внутренние компоненты были составными.

Здесь есть где разойтись. Однако остаюсь при своем мнении, особенно выполнения поиска, какие проблемы с производительностью имеют люди при использовании массивов. Они их имеют. Я нашел такое сообщение: "Почему бы вам не нормализовать данные? Это пофиксит все проблемы, включая многие проблемы, с которыми вы еще не столкнулись."

По мере углубления в это, в какой-то момент потребуется много тестов, чтобы понять, хорошо это или плохо.

Составные типы

Это еще один странный тип.

Когда вы создаете таблицу, вы также получаете созданный в то же время составной тип. Вы можете затем использовать составной тип в качестве столбца в другой таблице. Тем самым эта другая таблица фактически получает таблицу в своем столбце. Теперь, если это звучит сильно похожим на массив, я, скорее, соглашусь. Далее, нет ограничений, даже если они определены в вашей исходной таблице, которые будут применяться к составному типу. Кроме того, вы не можете создать никаких новых ограничений для составного типа.

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

CREATE TYPE microphone AS (

    waterproof boolean,

    speaker boolean,

    earpiece boolean

);

CREATE TABLE radio (

    radioname VARCHAR(50),

    mic microphone

);

INSERT INTO radio (mic,radioname) VALUES ('(1,1,0)','Yaesu 8900');

SELECT * FROM radio;

Вот результат:



ОК. Это довольно интересно. И опять, что происходит с индексированием? Производительностью? Тут еще многое нужно изучить.

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

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

• int4range - диапазон целых чисел (integer)


• int8range - диапазон больших целых (big integer)


• numrange - диапазон, основанный на типе numeric


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


• tstzrange - то же, что и выше, но с часовым поясом


• daterange - диапазон дат (date)

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

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

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

• regproc - хранимые процедуры и функции


• regrole - роли


• regtype - типы данных, наша статья

Интересно, что документация предупреждает об использовании oid и различных типов, которые будут приводить к "неоптимальному планированию". На этом основании я думаю, что это означает плохой выбор, который сделает оптимизатор при их использовании.

Типы доменов

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

Тип pg_lsn

Я не уверен, почему они не назвали его просто типом Log Sequence Number (LSN, последовательный номер журнала). Вы можете сохранять указатели на фактический LSN в журналах. Более того, вы можете написать функции для него, например, определить LSN, который больше (новее) заданного, и другие функции, относящиеся к журналу и записанным в нем транзакциям.

Псевдо-типы

Это, как бы лучше сказать, не совсем типы данных. Вы не можете использовать их в определениях столбца. Однако вы можете использовать их как аргументы функций и типы результата. Это действительно широкий список поведения. Позвольте привести несколько примеров:

• anyarray - функция примет любой тип массива


• void - функция, которая вообще не возвращает результата


• record - эта функция допускает любой вид неопределенной строки

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

Типы данных PostgreSQL

Я рад, что решил пройтись по всем этим различным типам данных. Теперь я могу увидеть существенные различия между PostgreSQL и другими реляционными хранилищами данных. Я также вижу, почему другие называют PostgreSQL объектной базой данных. Я не верю, что это так, но вы можете точно увидеть, откуда это происходит, автоматически создавая тип для таблицы. Меня также обнадежило наличие обычных типов данных, так что я могу выполнять стандартную работу, а также сумасшедшие вещи типа определения диапазонов данных в пределах отдельного столбца. Наконец, удивило, что, несмотря на множество типов данных, отсутствует географический тип. Конечно же, есть расширение, которое вы можете добавить для его поддержки, а это значит, что, вероятно, есть расширения для других всевозможных типов данных.