Databases — PostgreSQL
Базы данных — PostgreSQL
Базы данных — PostgreSQL
Общая теория SQL
Реляционные БД
Курс «PostgreSQL для начинающих»: #1 — Основы SQL
Слово "отношения" здесь ключевое, поскольку именно оно определяет, что ваша СУБД является реляционной - то есть в такой базе будут находиться связанные таблицы.
Потому что существуют достаточно много видов нереляционных СУБД (например базы данных временных рядов), преимущество которых заключается в возможности, хоть и жертвуя универсальностью, под каждую конкретную прикладную задачу подобрать наиболее подходящий из них: Key-Value, документарные, графовые, поисковые ("заточенные" под полнотекстовый или фразовый поиск) или даже мультипарадигмальные, приближающиеся по возможностям к традиционным SQL-базам.
Все эти варианты нереляционных СУБД никак не заставляют нас конкретизировать структуру хранения данных в нашей базе, и прямо рядом с ключом-числом можно положить ключ-строку или вовсе динамически заменить скалярное значение на список.
В отличие от них, в реляционных базах, структура жестко задается на моменте разработки, и ее нельзя быстро "перетряхнуть" в динамике - это достаточно сложный процесс. Безусловным стандартом работы с ними сейчас является именно SQL, универсальный по своим возможностям, его поддерживают все ведущие enterprise-СУБД.
. Как и любой язык, SQL выработал со временем определенные "диалекты", и каждая СУБД старается "создать" свой, чтобы сделать использование именно своих особенностей еще удобнее.
Поэтому, если у вас стоит задача писать максимально переносимый между СУБД софт, который будет все запросы формировать одинаково понятными для всех СУБД, то либо это будет очень сложным процессом, либо вы получите крайне неэффективные запросы, не использующие хоть какую-то специфику возможностей конкретной базы. То есть любой универсальный запрос на SQL будет одинаково выполняться на всех таких базах, но на всех - не настолько эффективно, насколько можно было бы сделать с учетом специфики.
Хранение данных в реляционных базах
Все базы нужны для того, чтобы хранить какие-то прикладные данные, и в SQL-ориентированных некоторый класс объектов - например, "документы" (бумажка с подписью), с атрибутами "номер" и "дата" - будет представлен отдельной таблицей.
Атрибуты объекта будут являться ее столбцами, экземпляры объектов - строками, а на пересечении - в поле конкретной строки - будет храниться значение данного атрибута для конкретного экземпляра (Номер = 123, Дата = 01.01.2000).
Поэтому все строки одной таблицы имеют один и тот же формат, в отличие от нереляционных баз.
Между собой таблицы связываются какими-то отношениями, которые определяются ключами.
Как правило, у любой таблицы есть первичный ключ (Primary Key, PK), и он необходим, чтобы уникально идентифицировать любую из строк этой таблицы.
Конечно, бывают ситуации, когда у вас в таблице по вполне определенным прикладными причинам может не быть первичного ключа. Например, вы пишете какую-то систему хранения логов, и запись данных в нее происходит настолько часто, что даже 1 секунда не является уникальным идентификатором - когда у вас таких событий происходит за секунду 10, вы их все пишете, а время отличить между ними никак не можете. И тогда вам или придется добавить в таблицу в качестве первичного суррогатный ключ, или вообще стоит его не делать - если отдельные записи не требуется уникально идентифицировать, а на нее никто не ссылается.
Потому что первичные ключи, классически, используются именно для того, чтобы иметь возможность сослаться на конкретную запись или провзаимодействовать с ней. А как раз чтобы "сослаться" со стороны подчиненной таблицы используются внешние ключи (Foreign Keys, FK) - они определяют по соответствию значений каких полей в дочерней и родительской таблице устанавливается связь.
В принципе, внешний ключ может ссылаться не обязательно на первичный, но и на любой уникальный ключ (Unique Key), которых на таблице, в отличие от первичного, у вас может быть несколько. Например, в таблице, куда вы записываете всех своих клиентов, первичным ключом может выступать суррогатный идентификатор, а дополнительным уникальным ключом - его ИНН.
Развитие стандарта SQL
Мы говорим об SQL, подразумевая, что это общепринятый стандарт, утвержденный ANSI и ISO, по которому даже разные сертификаты выдаются, ему уже 40 лет... Но, несмотря на столь почтенный, по меркам IT-технологий, возраст, это не какая-то замшелая скрижаль, на которой все давно высечено рунами.
Нет, это вполне живой организм, который активно развивается, и раз в 3-5 лет появляется новое расширение стандарта. И "бег" за поддержкой этого стандарта как раз и определяет путь развития современных СУБД.
Например, если взглянуть на стандарт 2016 года, то... работу с JSON пытались зарелизить в PostgreSQL 16, которая вышла в этом октябре, Row Level Security сделали еще в версии 15, если не раньше, в вот pattern matching только сейчас пытаются доработать для будущей версии 17, ровно как и JSON, поскольку финальный вариант патчей в v16 не вошел.
То есть на данный момент стандарт SQL по своей проработке опережает возможности реальных СУБД. Это ровно та самая разница между декларативным описанием в стандарте "как должно быть" и фактической реализацией на императивных языках "внутри" движка базы "как это должно работать".
Особенности PostgreSQL
Пока мы все говорили про SQL в целом, давайте теперь коснемся особенностей непосредственно PostgreSQL.
Во-первых, в отличие от некоторых других СУБД, PostgreSQL исповедует клиент-серверную архитектуру. Это означает, что у нас всегда есть некоторый клиент, который формирует запрос и по собственному протоколу "поверх" TCP/IP отправляет его серверу. Как правило, этот запрос текстовый и содержит какие-то SQL-команды. А в ответ мы получаем некоторый код результата и, возможно, выборку.
Для того, чтобы иметь возможность послать это все серверу и получить обратно, необходимо либо клиентское приложение, либо какая-то библиотека, необходимая для вашего приложения.
PostgreSQL является одной из наиболее популярных систем управления базами данных. Сам проект postgresql эволюционировал из другого проекта, который назывался Ingres. Формально развитие postgresql началось еще в 1986 году. Тогда он назывался POSTGRES. А в 1996 году проект был переименован в PostgreSQL, что отражало больший акцент на SQL. И собственно 8 июля 1996 года состоялся первый релиз продукта.
С тех пор вышло множество версий postgresql. Текущей версией является версия 17. Однако регулярно также выходят подверсии.
PostgreSQL поддерживается для всех основных операционных систем - Windows, Linux, MacOS.
Официальный сайт проекта: https://www.postgresql.org/.
PostgreSQL развивается как opensource. Исходный код проекта можно найти в репозитории на гитхабе по адресу https://github.com/postgres/postgres.
Основы PostgeSQL
Школа backend. PostgreSQL - YouTube
17: Документация к PostgreSQL 17.5 : Компания Postgres Professional
Типы данных
https://postgrespro.ru/docs/postgresql/16/datatype
Числовые типы
PostgreSQL : Документация: 17: 8.1. Числовые типы в PostgreSQL определяются своей разрядностью: 2-, 4- и 8-байтные целочисленные, 4- и 8-байтовые с переменной точностью (с плавающей точкой) и numeric/decimal с указанной точностью (хранится посимвольно).
Выбор между целочисленными типами достаточно прост: если все ожидаемые значения в пределах сотни, то не надо резервировать под них 8-байтовый bigint. Как правило, стандартного 4-байтового integer достаточно для большинства задач.
numeric стоит использовать для различных денежных единиц, где недопустимо "потерять копейку на округлениях":
serial-псевдотипы (аналог AUTO_INCREMENT / IDENTITY из других СУБД), которые позволяют определить поля с автоматически формируемым возрастающим значением "по умолчанию": 1, 2, 3, ...
нет unsigned - все числовые типы знаковые, поэтому "честно" положить диапазон [0x00000000..0xFFFFFFFF] в integer не получится, только со смещением "наполовину"
Символьные типы
PostgreSQL : Документация: 17: 8.3. Символьные типы : Компания Postgres Professional - представлены парой описанных в стандарте char/varchar и парой PostgreSQL-специфичных bpchar/text
Если вы не предполагаете перенос вашего приложения на другую СУБД, то можете спокойно использовать тип text везде, поскольку указание ограничения длины не дает никаких бонусов. Конечно, за исключением случаев, когда вам действительно требуется ограничить длину записываемого в поле - например, для 2-буквенного кода страны.
Типы даты/времени
PostgreSQL : Документация: 17: 8.5. Типы даты/времени в PostgreSQL, технически, хранятся как целочисленные, со значением от POSTGRES_EPOCH (01.01.2000) в соответствующих единицах (микросекундах или сутках):
В этом их отличие от некоторых других СУБД, где timestamp может храниться как текстовая строка.
А раз это просто числа, то арифметические операции над ними тоже допустимы, в том числе преобразование к Unix time (время от 01.01.1970) :
Опционально, во временном значении можно использовать часовой пояс (with time zone) или указывать сохраняемую точность (timestamp(0) означает хранение "до секунд").
Логический тип
PostgreSQL : Документация: 17: 8.6. Логический тип - представлены типом boolean:
Он может принимать значения TRUE/FALSE и, с учетом SQL-специфики, значение NULL, равно как и любой другой тип.
Специальные типы данных
Помимо базовых типов, "из коробки" PostgreSQL предоставляет массу других, более специализированных, типов:
двоичные данные
перечисления
геометрические
сетевые адреса
битовые строки
вектора текстового поиска
UUID
XML
JSON
массивы
диапазоны
Например, всякие картографические сервисы любят использовать геометрические типы данных с расширением PostGIS, а слабоструктурированные данные можно хранить в JSON, причем ничуть не хуже какой-нибудь MongoDB, а идентификаторы в распределенных системах - в UUID.
Схема
Схема в PostgreSQL - это логическая структура, которая представляет собой набор таблиц, индексов, представлений, функций и других объектов базы данных, организованных в единую группу. Схема помогает организовать данные в базе данных, делая их более удобными для управления и обеспечивая логическую разделяемость объектов базы данных.
С помощью схемы можно создавать отдельные пространства имен для объектов базы данных, что позволяет избежать конфликтов имен и обеспечивает более удобную организацию базы данных. Каждая таблица, индекс или другой объект базы данных может принадлежать к определенной схеме, что позволяет структурировать базу данных и упрощает ее обслуживание.
Схемы также играют важную роль при управлении правами доступа к данным, так как позволяют назначать различные права на доступ к объектам базы данных в зависимости от их принадлежности к определенной схеме.
Кластер баз данных PostgreSQL содержит один или несколько именованных экземпляров баз. На уровне кластера создаются роли и некоторые другие объекты. При этом в рамках одного подключения к серверу можно обращаться к данным только одной базы — той, что была выбрана при установлении соединения.
База данных содержит одну или несколько именованных схем, которые в свою очередь содержат таблицы. Схемы также содержат именованные объекты других видов, включая типы данных, функции и операторы. В одной схеме два объекта одного типа не могут иметь одинаковые имена. Более того, таблицы, последовательности, индексы, представления, материализованные представления и внешние таблицы существуют в одном пространстве имён, так что, например, имена индекса и таблицы должны отличаться, если они находятся в одной схеме. Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1, и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.
Есть несколько возможных объяснений, для чего стоит применять схемы:
Чтобы одну базу данных могли использовать несколько пользователей, независимо друг от друга.
Чтобы объединить объекты базы данных в логические группы для облегчения управления ими.
Чтобы в одной базе сосуществовали разные приложения, и при этом не возникало конфликтов имён.
Схемы в некотором смысле подобны каталогам в операционной системе, но они не могут быть вложенными.
Схема системного каталога
В дополнение к схеме public и схемам, создаваемым пользователями, любая база данных содержит схему pg_catalog, в которой находятся системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog фактически всегда является частью пути поиска. Если даже эта схема не добавлена в путь явно, она неявно просматривается до всех схем, указанных в пути. Так обеспечивается доступность встроенных имён при любых условиях. Однако вы можете явным образом поместить pg_catalog в конец пути поиска, если вам нужно, чтобы пользовательские имена переопределяли встроенные.
Так как имена системных таблиц начинаются с pg_, такие имена лучше не использовать во избежание конфликта имён, возможного при появлении в будущем системной таблицы с тем же именем, что и ваша. (С путём поиска по умолчанию неполная ссылка будет воспринята как обращение к системной таблице.) Системные таблицы будут и дальше содержать в имени приставку pg_, так что они не будут конфликтовать с неполными именами пользовательских таблиц, если пользователи со своей стороны не будут использовать приставку pg_.
Зачем нужны схемы:
Схемам можно найти множество применений. Для защиты от влияния недоверенных пользователей на поведение запросов других пользователей предлагается шаблон безопасного использования схем, но если этот шаблон не применяется в базе данных, пользователи, желающие безопасно выполнять в ней запросы, должны будут принимать защитные меры в начале каждого сеанса. В частности, они должны начинать каждый сеанс с присваивания пустого значения переменной search_path или каким-либо другим образом удалять из search_path схемы, доступные для записи обычным пользователям. С конфигурацией по умолчанию легко реализуются следующие шаблоны использования:
Ограничить обычных пользователей личными схемами. Для реализации этого подхода сначала убедитесь, что ни у одной схемы нет права CREATE. Затем для каждого пользователя, который будет создавать не временные объекты, создайте схему с его именем, например CREATE SCHEMA alice AUTHORIZATION alice. (Как вы знаете, путь поиска по умолчанию начинается с имени $user, вместо которого подставляется имя пользователя. Таким образом, если у всех пользователей будет отдельная схема, они по умолчанию будут обращаться к собственным схемам.) Этот шаблон позволяет безопасно использовать схемы, только если никакой недоверенный пользователь не является владельцем базы данных и не получал право ADMIN OPTION для соответствующей роли. В противном случае безопасное использование схем невозможно.
В PostgreSQL 15 и выше этот подход использования поддерживается конфигурацией по умолчанию. В предыдущих версиях или при использовании базы данных, обновлённой с предыдущей версии, необходимо удалить право CREATE из схемы public (выполнить REVOKE CREATE ON SCHEMA public FROM PUBLIC). Затем проверьте, нет ли в схеме public объектов с такими же именами, как у объектов в схеме pg_catalog.
Удалить схему public из пути поиска по умолчанию, изменив postgresql.conf или выполнив команду ALTER ROLE ALL SET search_path = "$user". Затем следует предоставить права на создание объектов в схеме public. Выбираться объекты в этой схеме будут только по полному имени. Тогда как обращаться к таблицам по полному имени вполне допустимо, обращения к функциям в общей схеме всё же будут небезопасными или ненадёжными. Поэтому если вы создаёте функции или расширения в схеме public, применяйте первый шаблон. Если же нет, этот шаблон, как и первый, безопасен при условии, что никакой недоверенный пользователь не является владельцем базы данных и не получал право ADMIN OPTION для соответствующей роли.
Сохранить путь поиска по умолчанию и предоставить права создания объектов в схеме public. Все пользователи неявно обращаются к схеме public. Тем самым имитируется ситуация с полным отсутствием схем, что позволяет осуществить плавный переход из среды без схем. Однако данный шаблон ни в коем случае нельзя считать безопасным. Он подходит, только если в базе данных имеется всего один либо несколько доверяющих друг другу пользователей. В базах данных, обновлённых с версии PostgreSQL 14 или более ранней, этот шаблон применяется по умолчанию.
При любом подходе, устанавливая совместно используемые приложения (таблицы, которые нужны всем, дополнительные функции сторонних разработчиков и т. д.), помещайте их в отдельные схемы. Не забудьте дать другим пользователям права для доступа к этим схемам. Тогда пользователи смогут обращаться к этим дополнительным объектам по полному имени или при желании добавят эти схемы в свои пути поиска.
Базовые запросы
Команда Create
Синтаксис - PostgreSQL : Документация: 17: CREATE TABLE : Компания Postgres Professional
CREATE TABLE создает новую, изначально пустую таблицу в текущей базе данных. Владельцем таблицы будет пользователь, выполнивший эту команду.
Если задано имя схемы (например, CREATE TABLE myschema.mytable ...), таблица создаётся в указанной схеме, в противном случае — в текущей. Временные таблицы существуют в специальной схеме, так что при создании таких таблиц имя схемы задать нельзя. Имя таблицы должно отличаться от имён других отношений (таблиц, последовательностей, индексов, представлений, материализованных представлений или сторонних таблиц) в этой схеме.
CREATE TABLE также автоматически создаёт составной тип данных, соответствующий одной строке таблицы. Таким образом, имя таблицы не может совпадать с именем существующего типа в этой же схеме.
Необязательные предложения ограничений задают ограничения (проверки), которым должны удовлетворять добавляемые или изменяемые строки, чтобы операция добавления или изменения была выполнена успешно. Ограничение представляет собой SQL-объект, помогающий некоторым способом определить множество допустимых значений в таблице.
Определить ограничения можно двумя способами: в виде ограничений таблицы и в виде ограничений столбца. Ограничение столбца определяется как часть определения столбца, а ограничение таблицы не привязывается к конкретному столбцу и может задействовать несколько столбцов. Любые ограничения столбцов можно также записать в виде ограничения таблицы, они введены просто для удобства записи в случаях, когда ограничение затрагивает только один столбец.
Чтобы создать таблицу, необходимо иметь право USAGE для типов всех столбцов или типа в предложении OF, соответственно.
Select
Курс «PostgreSQL для начинающих»: #2 — Простые SELECT
Подробный синтаксис - PostgreSQL : Документация: 17: SELECT : Компания Postgres Professional
Поговорим о самых простых, но важных, возможностях команды SELECT, наиболее часто используемой при работе с базами данных - формировании выборок (VALUES), их ограничении (LIMIT/OFFSET/FETCH), фильтрации (WHERE/HAVING), сортировке (ORDER BY), уникализации (DISTINCT) и группировке (GROUP BY).
Для того чтобы прочитать из таблицы все данные. В SQL сделать это очень просто - не надо писать ни циклов, ни итераторов, достаточно всего лишь:
SELECT FROM <имя_таблицы>;
Впрочем, можно даже не писать SELECT FROM, потому что есть команда TABLE, которая делает то же самое - безусловно вычитывает из таблицы строки со всеми полями в них:
TABLE <имя_таблицы>;
Но если вдруг кому-то начало казаться, что SELECT - это просто, то это совсем не так, это достаточно сложно, ведь SELECT - самая богатая по количеству функционала команда, которая только есть в SQL:
Формат результирующей выборки
Как минимум, он отличается тем, что мы описываем те столбцы, которые хотим в результирующей выборке видеть - список возвращаемых полей. Если VALUES формирует имена и определяет типы столбцов самостоятельно, то для SELECT нам необходимо хоть что-то написать самим относительно того, что пришло во FROM-части.
Это может быть некоторое:
выражение от столбцов из FROM,
имя любого из этих столбцов
или можем указать "" ("дай мне все столбцы из...") для части или всего FROM
В целом, если "на вход" из FROM нам пришла строка, то и "на выходе" мы получим строку соответствующую ей, как-то преобразованную указанными нами выражениями.
Хотя, FROM-части в SELECT-запросе может и не быть:
При этом мы можем задать имя столбца результирующей выборки с помощью ключевого слова AS, хотя и без него - тоже. А можем и не задавать - в этом случае, имя ему присвоено не будет, обращаться по нему мы не сможем, но получить значение - легко.
Как правило, все библиотеки и утилиты для работы с PostgreSQL, отдают результирующую выборку двумя массивами:
один описывает порядок, имена и типы столбцов,
а второй содержит строки ответа в виде объектов или массивов значений полей в соответствии с порядком описания столбцов.
Либо в качестве имени столбца будет взято имя вызываемой для его формирования функции (random, generate_series, coalesce, nullif, ...) или оператора CASE. Функция при этом может генерировать как единственное значение, так и сразу набор строк
Фильтрация исходной выборки (WHERE)
Следующее "предложение" - WHERE, определяет, какие из строк исходной выборки будут обработаны и сформируют строки результата, а какие будут отброшены.
Если формат выборки определяет, какие столбцы мы получим, то WHERE - какие строки. И правила тут просты - если указанное boolean-выражение дает для строки TRUE - она попадает в выборку, если FALSE или NULL - нет.
для сравнения в SQL используется "одинарный" оператор "=".
Сортировка Order by
PostgreSQL : Документация: 17: 7.5. Сортировка строк (ORDER BY)
После того как запрос выдал таблицу результатов (после обработки списка выборки), её можно отсортировать. Если сортировка не задана, строки возвращаются в неопределённом порядке. Фактический порядок строк в этом случае будет зависеть от плана соединения и сканирования, а также от порядка данных на диске, поэтому полагаться на него нельзя. Определённый порядок выводимых строк гарантируется, только если этап сортировки задан явно.
Порядок сортировки определяет предложение ORDER BY:
Выражениями сортировки могут быть любые выражения, допустимые в списке выборки запроса. Например:
SELECT a, b FROM table1 ORDER BY a + b, c;
Когда указывается несколько выражений, последующие значения позволяют отсортировать строки, в которых совпали все предыдущие значения. Каждое выражение можно дополнить ключевыми словами ASC или DESC, которые выбирают сортировку соответственно по возрастанию или убыванию. По умолчанию принят порядок по возрастанию (ASC). При сортировке по возрастанию сначала идут меньшие значения, где понятие «меньше» определяется оператором <. Подобным образом, сортировка по возрастанию определяется оператором >. [6]
Для определения места значений NULL можно использовать указания NULLS FIRST и NULLS LAST, которые помещают значения NULL соответственно до или после значений не NULL. По умолчанию значения NULL считаются больше любых других, то есть подразумевается NULLS FIRST для порядка DESC и NULLS LAST в противном случае.
Заметьте, что порядки сортировки определяются независимо для каждого столбца. Например, ORDER BY x, y DESC означает ORDER BY x ASC, y DESC, и это не то же самое, что ORDER BY x DESC, y DESC.
Здесь выражение_сортировки может быть меткой столбца или номером выводимого столбца, как в данном примере:
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
Оба эти запроса сортируют результат по первому столбцу. Заметьте, что имя выводимого столбца должно оставаться само по себе, его нельзя использовать в выражении. Например, это ошибка:
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- неправильно
Это ограничение позволяет уменьшить неоднозначность. Тем не менее неоднозначность возможна, когда в ORDER BY указано простое имя, но оно соответствует и имени выходного столбца, и столбцу из табличного выражения. В этом случае используется выходной столбец. Эта ситуация может возникнуть, только когда с помощью AS выходному столбцу назначается то же имя, что имеет столбец в другой таблице.
ORDER BY можно применить к результату комбинации UNION, INTERSECT и EXCEPT, но в этом случае возможна сортировка только по номерам или именам столбцов, но не по выражениям.
Поиск по шаблону LIKE
PostgreSQL : Документация: 17: 9.7. Поиск по шаблону
PostgreSQL предлагает три разных способа поиска текста по шаблону: традиционный оператор LIKE языка SQL, более современный SIMILAR TO (добавленный в SQL:1999) и регулярные выражения в стиле POSIX. Помимо простых операторов, отвечающих на вопрос «соответствует ли строка этому шаблону?», в PostgreSQL есть функции для извлечения или замены соответствующих подстрок и для разделения строки по заданному шаблону.
Хотя чаще всего поиск по регулярному выражению бывает очень быстрым, регулярные выражения бывают и настолько сложными, что их обработка может занять приличное время и объём памяти. Поэтому опасайтесь шаблонов регулярных выражений, поступающих из недоверенных источников. Если у вас нет другого выхода, рекомендуется ввести тайм-аут для операторов.
Поиск с шаблонами SIMILAR TO несёт те же риски безопасности, так как конструкция SIMILAR TO предоставляет во многом те же возможности, что и регулярные выражения в стиле POSIX.
Поиск с LIKE гораздо проще, чем два другие варианта, поэтому его безопаснее использовать с недоверенными источниками шаблонов поиска.
строка LIKE шаблон [ESCAPE спецсимвол]
строка NOT LIKE шаблон [ESCAPE спецсимвол]
Выражение LIKE возвращает true, если строка соответствует заданному шаблону. (Как можно было ожидать, выражение NOT LIKE возвращает false, когда LIKE возвращает true, и наоборот. Этому выражению равносильно выражение NOT (строка LIKE шаблон).)
Если шаблон не содержит знаков процента и подчёркиваний, тогда шаблон представляет в точности строку и LIKE работает как оператор сравнения. Подчёркивание (_) в шаблоне подменяет (вместо него подходит) любой символ; а знак процента (%) подменяет любую (в том числе и пустую) последовательность символов.
При проверке по шаблону LIKE всегда рассматривается вся строка. Поэтому, если нужно найти последовательность символов где-то в середине строки, шаблон должен начинаться и заканчиваться знаками процента.
Чтобы найти в строке буквальное вхождение знака процента или подчёркивания, перед соответствующим символом в шаблоне нужно добавить спецсимвол. По умолчанию в качестве спецсимвола выбрана обратная косая черта, но с помощью предложения ESCAPE можно выбрать и другой. Чтобы включить спецсимвол в шаблон поиска, продублируйте его.
Также можно отказаться от спецсимвола, написав ESCAPE ‘ '. При этом механизм спецпоследовательностей фактически отключается и использовать знаки процента и подчёркивания буквально в шаблоне нельзя.
Согласно стандарту SQL, отсутствие указания ESCAPE означает, что спецсимвол не определён (то есть спецсимволом не будет обратная косая черта), а пустое значение в ESCAPE не допускается. Таким образом, в этом поведение PostgreSQL несколько отличается от оговорённого в стандарте.
Вместо LIKE можно использовать ключевое слово ILIKE, чтобы поиск был регистр-независимым с учётом текущей языковой среды. Этот оператор не описан в стандарте SQL; это расширение PostgreSQL.
Кроме того, в PostgreSQL есть оператор ~~, равнозначный LIKE, и ~~, соответствующий ILIKE. Есть также два оператора !~~ и !~~, представляющие NOT LIKE и NOT ILIKE, соответственно. Все эти операторы относятся к особенностям PostgreSQL. Вы можете увидеть их, например, в выводе команды EXPLAIN, так как при разборе запроса проверка LIKE и подобные заменяются ими.
Фразы LIKE, ILIKE, NOT LIKE и NOT ILIKE в синтаксисе PostgreSQL обычно обрабатываются как операторы; например, их можно использовать в конструкциях выражение оператор ANY (подвыражение), хотя предложение ESCAPE здесь добавить нельзя. В некоторых особых случаях всё же может потребоваться использовать вместо них нижележащие операторы.
Также обратите внимание на оператор проверки начала строки ^@ и соответствующую функцию starts_with(), которые полезны в случаях, когда нужно произвести сопоставление только с началом строки.
Функции и операторы сравнения
В стандарте SQL для условия «не равно» принята запись <>. Синонимичная ей запись != преобразуется в <> на самой ранней стадии разбора запроса. Как следствие, реализовать операторы != и <> так, чтобы они работали по-разному, невозможно.
Эти операторы сравнения имеются для всех встроенных типов данных, значения которых сортируются естественным образом, включая числовые, строковые типы, а также типы даты/времени. Кроме того, сравниваться могут массивы, составные типы и диапазоны, если типы данных их компонентов являются сравниваемыми.
Обычно можно сравнивать также значения связанных типов данных; например, возможно сравнение integer > bigint. Некоторые подобные операции реализуются непосредственно «межтиповыми» операторами сравнения, но если такого оператора нет, анализатор запроса попытается привести частные типы к более общим и применить подходящий для них оператор сравнения.
Как показано выше, все операторы сравнения являются бинарными и возвращают значения типа boolean. Таким образом, выражения вида 1 < 2 < 3 недопустимы (так как не существует оператора <, который бы сравнивал булево значение с 3). Для проверки нахождения значения в интервале, воспользуйтесь предикатом BETWEEN, описанным ниже.
Существует также несколько предикатов сравнения; они приведены в таблице. Они работают подобно операторам, но имеют особый синтаксис, установленный стандартом SQL.
Предикат BETWEEN упрощает проверки интервала:
a BETWEEN x AND y
равнозначно
a >= x AND a <= y
Заметьте, что BETWEEN считает, что границы интервала включаются в интервал. Предикат BETWEEN SYMMETRIC аналогичен BETWEEN, за исключением того, что аргумент слева от AND не обязательно должен быть меньше или равен аргументу справа. Если это не так, аргументы автоматически меняются местами, так что всегда подразумевается непустой интервал.
Различные варианты BETWEEN реализуются посредством обычных операторов сравнения, и поэтому они будут работать с любыми типами данных, которые можно сравнивать.
Обычные операторы сравнения выдают NULL (что означает «неопределённость»), а не true или false, когда любое из сравниваемых значений NULL. Например, 7 = NULL выдаёт NULL, так же, как и 7 <> NULL. Когда это поведение нежелательно, можно использовать предикаты IS [ NOT ] DISTINCT FROM:
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b
Для значений не NULL условие IS DISTINCT FROM работает так же, как оператор <>. Однако если оба сравниваемых значения NULL, результат будет false, и только если одно из значений NULL, возвращается true. Аналогично, условие IS NOT DISTINCT FROM равносильно = для значений не NULL, но возвращает true, если оба сравниваемых значения NULL, и false в противном случае. Таким образом, эти предикаты по сути работают с NULL, как с обычным значением, а не с «неопределённостью».
Для проверки, содержит ли значение NULL или нет, используются предикаты:
выражение IS NULL
выражение IS NOT NULL
или равнозначные (но нестандартные) предикаты:
выражение ISNULL
выражение NOTNULL
Заметьте, что проверка выражение = NULL не будет работать, так как NULL считается не «равным» NULL. (Значение NULL представляет неопределённость, и равны ли две неопределённости, тоже не определено.)
Insert
Подробно - PostgreSQL : Документация: 17: INSERT : Компания Postgres Professional
INSERT добавляет строки в таблицу. Эта команда может добавить одну или несколько строк, сформированных выражениями значений, либо ноль или более строк, выданных дополнительным запросом.
Имена целевых столбцов могут перечисляться в любом порядке. Если список с именами столбцов отсутствует, по умолчанию целевыми столбцами становятся все столбцы заданной таблицы; либо первые N из них, если только N столбцов поступает от предложения VALUES или запроса. Значения, получаемые от предложения VALUES или запроса, связываются с явно или неявно определённым списком столбцов слева направо.
Все столбцы, не представленные в явном или неявном списке столбцов, получат значения по умолчанию, если для них заданы эти значения, либо NULL в противном случае.
Если выражение для любого столбца выдаёт другой тип данных, система попытается автоматически привести его к нужному.
Выводимая информация
В случае успешного завершения INSERT возвращает метку команды в виде
INSERT oid число
Здесь число представляет количество добавленных или изменённых строк. Поле oid всегда содержит 0 (раньше в нём выводился OID, присвоенный добавленной строке, когда число равнялось 1 и целевая таблица была создана с указанием WITH OIDS, а в противном случае — 0; теперь же создание таблицы с характеристикой WITH OIDS не поддерживается).
Если команда INSERT содержит предложение RETURNING, её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING), полученный для строк, добавленных или изменённых этой командой.
Добавление одной строки в таблицу films:
INSERT INTO films VALUES
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
В этом примере столбец len опускается и, таким образом, получает значение по умолчанию:
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
В этом примере для столбца с датой задаётся указание DEFAULT, а не явное значение:
INSERT INTO films VALUES
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
Добавление строки, полностью состоящей из значений по умолчанию:
INSERT INTO films DEFAULT VALUES;
Update
Подробно - PostgreSQL : Документация: 17: UPDATE : Компания Postgres Professional
Описание
UPDATE изменяет значения указанных столбцов во всех строках, удовлетворяющих условию. В предложении SET должны указываться только те столбцы, которые будут изменены; столбцы, не изменяемые явно, сохраняют свои предыдущие значения.
Изменить строки в таблице, используя информацию из других таблиц в базе данных, можно двумя способами: применяя вложенные запросы или указав дополнительные таблицы в предложении FROM. Выбор предпочитаемого варианта зависит от конкретных обстоятельств.
Предложение RETURNING указывает, что команда UPDATE должна вычислить и возвратить значения для каждой фактически изменённой строки. Вычислить в нём можно любое выражение со столбцами целевой таблицы и/или столбцами других таблиц, упомянутых во FROM. При этом в выражении будут использоваться новые (изменённые) значения столбцов таблицы. Список RETURNING имеет тот же синтаксис, что и список результатов SELECT.
Для выполнения этой команды необходимо иметь право UPDATE для таблицы, или как минимум для столбцов, перечисленных в списке изменяемых. Также необходимо иметь право SELECT для всех столбцов, значения которых считываются в выражениях или условии.
Выводимая информация
В случае успешного завершения, UPDATE возвращает метку команды в виде
UPDATE число
Здесь число обозначает количество изменённых строк, включая те подлежащие изменению строки, значения в которых не были изменены. Заметьте, что это число может быть меньше количества строк, удовлетворяющих условию, когда изменения отменяются триггером BEFORE UPDATE. Если число равно 0, данный запрос не изменил ни одной строки (это не считается ошибкой).
Если команда UPDATE содержит предложение RETURNING, её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING), полученный для строк, изменённых этой командой.
Примеры
Изменение слова Drama на Dramatic в столбце kind таблицы films:
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
Изменение значений температуры и сброс уровня осадков к значению по умолчанию в одной строке таблицы weather:
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
WHERE city = 'San Francisco' AND date = '2003-07-03';
Выполнение той же операции с получением изменённых записей:
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
WHERE city = 'San Francisco' AND date = '2003-07-03'
RETURNING temp_lo, temp_hi, prcp;
Такое же изменение с применением альтернативного синтаксиса со списком столбцов:
UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
WHERE city = 'San Francisco' AND date = '2003-07-03';
Delete
Подробно - PostgreSQL : Документация: 17: DELETE : Компания Postgres Professional
Команда DELETE удаляет из указанной таблицы строки, удовлетворяющие условию WHERE. Если предложение WHERE отсутствует, она удаляет из таблицы все строки, в результате будет получена рабочая, но пустая таблица.
TRUNCATE реализует более быстрый механизм удаления всех строк из таблицы.
Удалить строки в таблице, используя информацию из других таблиц в базе данных, можно двумя способами: применяя вложенные запросы или указав дополнительные таблицы в предложении USING. Выбор предпочитаемого варианта зависит от конкретных обстоятельств.
Предложение RETURNING указывает, что команда DELETE должна вычислить и возвратить значения для каждой фактически удалённой строки. Вычислить в нём можно любое выражение со столбцами целевой таблицы и/или столбцами других таблиц, упомянутых в USING. Список RETURNING имеет тот же синтаксис, что и список результатов SELECT.
Чтобы удалять данные из таблицы, необходимо иметь право DELETE для неё, а также право SELECT для всех таблиц, перечисленных в предложении USING, и таблиц, данные которых считываются в условии.
Выводимая информация
В случае успешного завершения, DELETE возвращает метку команды в виде
DELETE число
Здесь число — количество удалённых строк. Заметьте, что это число может быть меньше числа строк, соответствующих условию, если удаления были подавлены триггером BEFORE DELETE. Если число равно 0, это означает, что запрос не удалил ни одной строки (это не считается ошибкой).
Если команда DELETE содержит предложение RETURNING, её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING), полученный для строк, удалённых этой командой.
Примечания
PostgreSQL позволяет ссылаться на столбцы других таблиц в условии WHERE, когда эти таблицы перечисляются в предложении USING. Например, удалить все фильмы определённого продюсера можно так:
DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';
По сути в этом запросе выполняется соединение таблиц films и producers, и все успешно включённые в соединение строки в films помечаются для удаления. Этот синтаксис не соответствует стандарту. Следуя стандарту, эту задачу можно решить так:
DELETE FROM films
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
В ряде случаев запрос в стиле соединения легче написать и он может работать быстрее, чем в стиле вложенного запроса.
Примеры
Удаление всех фильмов, кроме мюзиклов:
DELETE FROM films WHERE kind <> 'Musical';
Очистка таблицы films:
DELETE FROM films;
Удаление завершённых задач с получением всех данных удалённых строк:
DELETE FROM tasks WHERE status = 'DONE' RETURNING ;
Удаление из tasks строки, на которой в текущий момент располагается курсор c_tasks:
DELETE FROM tasks WHERE CURRENT OF c_tasks;
Особенности PostgreSQL
Кортежи
Как Postgres хранит строки
Использование дискового пространства | Документация Selectel
Autovacuum
PostgreSQL : Документация: 17: 24.1. Регламентная очистка
PostgreSQL : Документация: 17: 19.10. Автоматическая очистка
Настройка автовакуумирования в PostgreSQL - важно понимать для чего нужна автоматическая очистка и как она работает. Как именно настраивать - понимать не обязательно.
Продвинутые запросы + теория
Агрегатные функции
Подробно - Postgres Pro Standard : Документация: 10: 9.20. Агрегатные функции
В PostgreSQL агрегатные функции используются для вычисления единого значения на основе набора строк таблицы. Они применяются для получения сводной информации, такой как сумма, среднее значение, минимум, максимум и количество. PostgreSQL предоставляет встроенные агрегатные функции, а также поддерживает возможность создания пользовательских.
Вот некоторые из наиболее часто используемых агрегатных функций:
AVG(выражение): Вычисляет среднее значение для указанного столбца или выражения.
SUM(выражение): Вычисляет сумму значений в указанном столбце или выражении.
MIN(выражение): Возвращает минимальное значение в указанном столбце или выражении.
MAX(выражение): Возвращает максимальное значение в указанном столбце или выражении.
COUNT(*): Возвращает количество строк в результате запроса, включая строки с NULL значениями.
COUNT(выражение): Возвращает количество строк, в которых указанное выражение не равно NULL.
STRING_AGG(expression, delimiter): Соединяет значения из столбца expression в одну строку, используя указанный delimiter.
Пример использования:
Этот запрос вернет среднюю цену, общую сумму, минимальную цену, максимальную цену и общее количество продуктов в таблице products.
Кроме того, агрегатные функции часто используются в сочетании с оператором GROUP BY, чтобы вычислять агрегаты для каждой группы строк, удовлетворяющих определенным условиям. Например:
GROUP BY
Коротко - PostgreSQL : Документация: 17: 7.2. Табличные выражения
Подробно - PostgreSQL | Группировка
Объединения (join и union)
PostgreSQL | Неявное соединение таблиц - вся 6 глава
Индексы
Предположим, что у нас есть такая таблица:
и приложение выполняет много подобных запросов:
SELECT content FROM test1 WHERE id = константа;
Если система не будет заранее подготовлена, ей придётся сканировать всю таблицу test1, строку за строкой, чтобы найти все подходящие записи. Когда таблица test1 содержит большое количество записей, а этот запрос должен вернуть всего несколько (возможно, одну или ноль), такое сканирование, очевидно, неэффективно. Но если создать в системе индекс по полю id, она сможет находить строки гораздо быстрее. Возможно, для этого ей понадобится опуститься всего на несколько уровней в дереве поиска.
Подобный подход часто используется в технической литературе: термины и понятия, которые могут представлять интерес, собираются в алфавитном указателе в конце книги. Читатель может просмотреть этот указатель довольно быстро и затем перейти сразу к соответствующей странице, вместо того, чтобы пролистывать всю книгу в поисках нужного материала. Так же, как задача автора предугадать, что именно будут искать в книге читатели, задача программиста баз данных — заранее определить, какие индексы будут полезны.
Создать индекс для столбца id рассмотренной ранее таблицы можно с помощью следующей команды:
CREATE INDEX test1_id_index ON test1 (id);
Имя индекса test1_id_index может быть произвольным, главное, чтобы оно позволяло понять, для чего этот индекс.
Для удаления индекса используется команда DROP INDEX. Добавлять и удалять индексы можно в любое время.
Когда индекс создан, никакие дополнительные действия не требуются: система сама будет обновлять его при изменении данных в таблице и сама будет использовать его в запросах, где, по её мнению, это будет эффективнее, чем сканирование всей таблицы. Вам, возможно, придётся только периодически запускать команду ANALYZE для обновления статистических данных, на основе которых планировщик запросов принимает решения. В Главе 14 вы можете узнать, как определить, используется ли определенный индекс и при каких условиях планировщик может решить не использовать его.
Индексы могут быть полезны также при выполнении команд UPDATE и DELETE с условиями поиска. Кроме того, они могут применяться в поиске с соединением. То есть, индекс, определенный для столбца, участвующего в условии соединения, может значительно ускорить запросы с JOIN.
Создание индекса для большой таблицы может занимать много времени. По умолчанию PostgreSQL позволяет параллельно с созданием индекса выполнять чтение (операторы SELECT) таблицы, но операции записи (INSERT, UPDATE и DELETE) блокируются до окончания построения индекса. Для производственной среды это ограничение часто бывает неприемлемым. Хотя есть возможность разрешить запись параллельно с созданием индексов, при этом нужно учитывать ряд оговорок — они описаны в подразделе Неблокирующее построение индексов.
После создания индекса система должна поддерживать его в состоянии, соответствующем данным таблицы. С этим связаны неизбежные накладные расходы при изменении данных. Таким образом, индексы, которые используются в запросах редко или вообще никогда, должны быть удалены.
Вложенные запросы
PostgreSQL | Подзапросы
PostgreSQL подзапросы (Subqueries) — Oracle PL/SQL
Транзакции в PostgreSQL
Postgres Pro Standard : Документация: 17: 3.4. Транзакции
Транзакции PostgreSQL, Требования ACID, примеры. Подготовка к собеседованию, изучение / Хабр
Профилирование БД
План запроса (Explain)
Postgres Pro Standard : Документация: 17: EXPLAIN
Курс «PostgreSQL для начинающих»: #4 — Анализ запросов (ч.1 — как и зачем читать планы)
Аномалии под нагрузкой в PostgreSQL: о чём стоит помнить и с чем надо бороться
PG_stat
Postgres Pro Standard : Документация: 17: 26.2. Система накопительной статистики - документация
Анализ статистики для мониторинга PostgreSQL | Статья | Сообщество Directum -как можно пользоваться таблицами со статистикой
Pg_profiler и PWR
На установку не обращаем внимания - упор делаем на то, что можем увидеть и какие выводы можем из этого сделать.
pg_profile и pgpro_pwr: анализируем производительность БД
Как пользоваться pg_profile для анализа статистики в Postgres
Мониторинг БД
Основы мониторинга PostgreSQL. Алексей Лесовский
Важные метрики БД:
Кол-во транзакций проведенных в БД (чаще всего соотносится с кол-вом отправленных запросов в систему)
Процентиль времени выполнения транзакций на БД
Кол-во подключений\коннектов к БД системы – чаще всего является узким местом (на графике выглядит как прямая линия)
Системные метрики сервера, на котором БД находится (ЦПУ - память)
Блокировки в БД - внеплановое резкое изменение количества блокировок - повод задуматься и узнать какие запросы вызывают данное поведение.
Метрики autovacuum
Основные метрики autovacuum:
autovacuum_count: Количество запусков autovacuum для конкретной таблицы.
autovacuum_vacuum_count: Количество выполненных операций VACUUM в рамках autovacuum.
autovacuum_analyze_count: Количество выполненных операций ANALYZE в рамках autovacuum.
autovacuum_freeze_count: Количество выполненных операций по замораживанию кортежей в рамках autovacuum.
autovacuum_vacuum_bytes: Количество байт, обработанных операцией VACUUM.
autovacuum_analyze_bytes: Количество байт, обработанных операцией ANALYZE.
autovacuum_freeze_bytes: Количество байт, обработанных операцией замораживания.
autovacuum_last_vacuum: Время последнего выполнения VACUUM.
autovacuum_last_analyze: Время последнего выполнения ANALYZE.
autovacuum_start_time: Время начала последней операции autovacuum.
autovacuum_duration: Продолжительность последней операции autovacuum.
☠️ Jmeter
Git
🤡 Java
Основы
Основы языка Java — это фундамент, на котором строятся все приложения. Они включают базовые конструкции языка.
Структура программы
Типы данных
Операторы
Условия и циклы
Ввод\вывод