Конструктор sql запросов postgresql

Обновлено: 05.05.2024

Что если для того чтобы выполнить базовые CRUD операции:

сохранить список Person -ов

вычитать всё из таблицы

удалить все записи в таблице

удалить по имени

будет достаточно создать интерфейс:

а имплеметнация будет сгенерирована автоматически.

Напоминает Spring Data? Но это не Spring, не Hibernate и даже не JPA.

Kotlin-центричная библиотека (не фреймворк)

Не ORM (не содержит JPA)

Генерирует SQL и JDBC до этапа компиляции (Kotlin Annotation Precessing)

Нет магии в рантайме

Сгенерированный код отформатирован, можно дебажить, работает навигация, можно скопировать в проект и модифицировать

Удобный DSL для работы с базой

Есть 2 имплементации: под Postgres и Sqlite

Конфигурация

На данный момент есть 2 реализации этой библиотеки: для Postgresql и Sqlite. В данной статье примеры будут для Sqlite.

Для начала нужно сконфигурировать Gradle (да простят меня пользователи Maven):

Пояснения по build.gradle.kts

Добавить плагин для обработки аннотаций и генерации кода (`kapt`).

Добавить зависимость на core -часть библиотеки. Она содержит необходимые аннотации, и некоторый обвязочный код.

Сериализация в/из JSON используется для вложенных коллекций.

Непосредственно драйвер Sqlite базы.

Плагин создаст kapt конфигурацию, в которую нужно включить зависимость на `kapt`-часть библиотеки. Именно она занимается генерацией SQL запросов и кода JDBC.

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

Репозиторий

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

Команда ./gradlew kaptKotlin сгенерирует:

Первый запрос

Kotlite знает достаточно, чтобы сгенерировать код для этого запроса:

Из возвращаемого типа List следует, что записей может быть от 0 до N

Из возвращаемого типа Person следует, что каждый кортеж будет содержать три поля: id , name и birth_date .

По конвенции, для поля в классе birthDate ожидается значение в кортеже birth_date

В результате сгенерируется метод:

Как выполнить этот запрос?

В конфигурации ( build.gradle.kts ) мы указывали, что нужно сгенерировать класс my.pkg.DB . Это главный объект, через который осуществляется доступ ко всем сгенерированным репозиториям. Для его создания нужен DataSource . Все объявленные нами репозитории доступны внутри транзакции:

Запрос с параметрами

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

Возвращаемые типы

В зависимости от возвращаемого типа Kotlite генерирует различное поведение.

Список (List)

Cамый обычный тип. Полностью соответствует тому, что возвращает база от 0 до N элементов. Другие коллекции не поддерживаются.

Сущность (Entity)

На первый взгляд ничего особенного, но есть несколько нюансов:

что если запрос не вернет ни одного значения

что если запрос вернет больше одного значения

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

Для выбора первого значения можно пометить метод аннотацией kotlite.annotations.First

Скаляр

Возвращаемым типом может быть не только сущность, но и любое скалярное ("примитивное") значение. Например: Int , String , UUID LocalDate и т.п.

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

Для выбора первого значения можно пометить метод аннотацией kotlite.annotations.First

Nullable значения

Скаляр или сущность могут быть объявлены как Nullable . В таком случае вернется null если запрос не вернул ни одной записи.

Постраничный вывод (Pagination)

Pageable определяет сколько элементов размещается на странице, и какую страницу нужно выбрать

Генерация SQL

Все что мы рассмотрели до этого – была в основном генерация JDBC кода. SQL запросы нужно было писать разработчику самостоятельно. Но во многих случаях запросы выглядят тривиально, и могут быть сгенерированы автоматически.

Для этого нужно дать библиотеке немного информации о том, с какой сущностью мы работаем. Делается это через переменную типа, интерфейса kotlite.aux.Repository

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

Известно название таблицы. По конвенции это имя клaсса, сконвертированное из UpperCamelCase в snake_case . Название таблицы может быть явно указано в аннотации kotlite.annotations.Table .

Также известно количество, названия и типы колонок таблицы. Названия колонок конвертируются из camelCase в snake_case Альтернативно, название может быть указано в аннотации kotlite.annotations.Column

Что это нам дает?

Сохранение и обновление

Для любого метода, имя которого начинается на save (либо который помечен аннотацией kotlite.annotations.Save ) будет сгенерирован INSERT . Такой метод должен принимать в качестве параметро либо саму сущность, либо список сущностей. Возвращаемый тип должен быть Unit

Если сущность имеет первичный ключ (как минимум одно из полей помечено аннотацией kotlite.annotations.ID ) будет сгенерирован INSERT/UPDATE

Это поведение можно переопределить аннотацией:

Оптимистическая блокировка

Если числовое поле класса помечено аннотацией kotlite.annotations.Version для такой сущности запросы обновления и удаления будут содержать проверку текущей версии

Удаление

Для любого метода, имя которого начинается на delete (или который помечен аннотацией kotlite.annotations.Delete ) будет сгенерирован DELETE

Такой метод может принимать сущность в качестве параметра:

Удаление будет происходить по всем полям сущности

Если сущность имеет первичный ключ (хотя бы одно поле помечено kotlite.annotations.Id ) – удаление будет по первичному ключу:

Кроме этого метод удаления может так-же принимать и другие параметры, см. разделы "Метод с параметрами" и "Сложные условия" ниже.

Метод без параметров

Любой метод, объявленный в репозитории, считается запросом типа SELECT (кроме методов, названия которых начинаются со слов save и delete ).

Функции fun selectAll(): List и fun blaBlaBla(): List ничем не отличаются друг от друга и для них будет сгенерирован абсолютно одинаковый код.

Метод с параметрами

Все параметры метода должны совпадать по названию с полями класса. Они будут использованы как условия равенства во WHERE и объединены через AND .

Сложные условия

Её содержимое будет подставлено в запрос почти без изменений.

Сортировка

Часто вместе с постраничным выводом необходимо задать порядок:

Вложенные объекты

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

Альтернативно вложенные объекты могут быть сериализованы в JSON. Предмет для добавления в ближайшие версии.

Вложенные коллекции

Вложенные коллекции не могут быть представлены как связь один-ко-многим . Вместо этого они автоматически сериализуются в JSON.

Особенности (сравнительно с JPA/Hibernate)

Из-за использования SQL, рефакторинг (например, переименование поля сущности) может потребовать изменения тех запросов, которые были написаны вручную.

Поскольку во главу угла поставлена простота, нет возможности создавать связи `один-к-одному`, `один-ко-многим` (и нет N+1 проблемы).

Нет ленивых загрузок (и нет `SessionClosedException`).

Нет встроенного механизма конвертеров типов (не переусложнен API, библиотека решает только одну задачу).

Нет возможности сохранения иерархий наследования (в основном из-за личной неприязни автора к наследованию. Возможно будет добавлено в будущем).

Конференция PG Day Russia растет и масштабируется: этим летом мы готовим для вас доклады и тренинги по всем наиболее популярным базам данных, а также по администрированию и хранению данных. в рамках подготовки мы запустили корпоративный блог, где планируем делиться ценной информацией о происходящем в мире баз данных. Первый пост посвящен инструментам разработки для PostgreSQL, его автор varanio будет рад ответить на ваши вопросы и комментарии!

Я поспрашивал различных людей, имеющих отношение к PostgreSQL, что они используют в реальной жизни для разработки приложений, использующих PG.

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

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

Итак, вот неформальный топ и субъективное описание.

На первом месте psql, и это неудивительно. Надежный как автомат калашникова, бесплатный, стоит из коробки, что еще надо для счастья? Для редактирования запросов используется редактор, указанный в переменной окружения EDITOR, обычно ставят vim, nano или что-то в этом духе. Ну и вообще, psql — это unix-way, т.е. можно его запускать со своим редактором, своим пейджером для отображения результатов, ему можно на вход подавать sql-запрос через пайп, и вывод направлять куда надо.

Из минусов можно отметить слабенький автокомплит, а также то, что приходится заучивать неинтуитивные команды из серии \d \dt+ \sf и т.д. (впрочем, все описания команд доступны через команду \? )

Ну, и работа в консоли и в виме — это не всех устраивает почему-то :)

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

datagrip

IDE для баз. Несмотря на то, что продукт относительно свежий, он уже используется повсеместно. В основном за счет того, что сразу встроен в мегапопулярные продукты от компании JetBrains: IntelliJ IDEA, PyCharm, PhpStorm и т.д.

Собственно, эта его встроенность одновременно является и главной киллер-фичей продукта: вы редактируете, например, php-код, в котором есть строка с sql-запросом, и внезапно понимаете, что IDE вам подсказывает (прямо в вашем коде) синтаксис SQL, названия таблиц и их полей, подчеркивает красненьким, если что-то написано не так, форматирует SQL и многое-многое другое. Конечно, в этом же IDE можно делать и то, что умеют другие GUI для баз: просматривать списки таблиц и других сущностей, отдельно делать запросы, экспорт таблиц в разные форматы и многое другое.

Из особенностей я бы отметил следующие вещи:

можно выделить несколько insert'ов и нажать "Edit as table" (см. картинку). После чего отредактировать это в удобном табличном виде вместо sql-синтаксиса, причем там же можно добавлять строки, колонки, экспортировать в csv и т.д.
Можно сравнивать результаты двух запросов. Это полезно, когда пытаешься упростить сложный запрос, и при этом ничего не сломать.
встроенность в код проработана не до конца. К примеру, при переименовывании в каком-либо интерфейсе колонки таблицы, IDE не находит нужные строки с SQL в коде (при этом автокомплит в этих строках работал), и наоборот, находит какую-то чушь.
Визуальной разработки не очень много. Т.е. вы можете сделать таблицу, но view уже не можете. Если таблица содержит какие-то id с foreign key (допустим, ссылка на некий словарь), хотелось бы при в вводе данных в таблицу выбирать значения из словаря, а не вбивать айдишки.
Если посмотреть таблицу в какой-нибудь из схем, то Datagrip посылает запрос set search_path = имясхемы, что приводит к плохим последствиям, если используется pgbouncer (а он используется почти всегда в случае с php или когда много серверов), так что для dev-разработки лучше использовать разные подключения: для работы кода — через pgbouncer, для ide — напрямую к базе.

Datagrip активно развивается, в частности, исправлены некоторые раздражающие баги с подсветкой синтаксиса.

В целом хороший современный инструмент, рекомендую.

pgAdmin

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

Как его использовать с точки зрения разработки — еще менее понятно. Субъективно, интерфейс в целом не удобен для разработки. Несмотря на то, что четвертую версию переписали на python + JS с jQuery, по сути, осталось всё то же самое.

Чтобы немного пояснить ситуацию, в голове разработчика такая картина: есть база на каком-то серваке, в ней — схемы, в схемах — таблицы и вьюхи. Т.е. таблица — максимум, 3-й уровень. А если база одна, то вообще второй уровень. Ткнул по таблице — увидел несколько первых строк.

В голове разработчика pgAdmin как-то так: "Смерть Кощеева на конце иглы, та игла в яйце, то яйцо в утке, та утка в зайце, тот заяц в сундуке, а сундук стоит на высоком дубу, и то дерево Кощей как свой глаз бережёт", а именно (см. картинку):

Есть группа серверов, в ней есть сервер, на сервере существуют базы, роли и т.д., из баз можно выбрать конкретную базу, в ней видно схемы, языки, еще бог знает что. В схемах можно выбрать нужную схему, в схеме 100500 всего, и где-то в конце списка "таблицы". В таблицах можно выбрать нужную таблицу, по ней надо кликнуть правой кнопкой мыши, там в большом списке выбираешь "view data", в этой "view data" есть "view first 100 rows" и уже там наконец-то смерть кощеева несколько строк для ознакомления.

Киллер-фичей pgAdmin является возможность дебажить хранимые процедуры pl/pgsql. Других бесплатных программ с этой возможностью я не встречал.

EMS Studio

EMS Studio, похоже, работает только под Windows. Это его главный недостаток, потому что, как известно PostgreSQL очень редко используют под виндой.

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

До кучи там зачем-то сделан визуальный конструктор запросов. Где вместо того, чтобы текстом написать where >, надо нажать мышкой несколько кнопок и понавыбирать из выпадающего списка. Тем, кто знает SQL — это не нужно, тем кто не знает — это не поможет.

Фичи, которые называют как удобные: auto-complete с алиасами, экспорт результата выполнения запроса в SQL формате (insert), удобный GUI для экпорта базы, возможность выполнять только выделенную часть SQL.

Умеет дебаг pl/pgsql. В общем, много чего умеет, но какой-то выдающейся особенности, что отличало бы от других, я не могу назвать.

NAVICAT

Navicat — это, наверное, самая богатая фичами программа. Она умеет всё, что умеют другие GUI для БД: дизайнер объектов, просмотрщик таблиц, автокомплит, инструменты проектирования базы, отладка pl/pgsql, импорт/экспорт и так далее.

Поистине всеобъемлющий софт, который работает практически на любой ОС. Навскидку, намного удобнее EMS Studio.

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

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

PGCLI

Те, кто пробует работать с psql, сразу начинают мечтать о более богатом функционале, например, автодополнении. Для реализации этих хотелок существует pgcli.

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

Короче, pgcli — это, по сути, psql на стероидах.

phppgadmin

Многие из тех, кто перешел с MySQL, инстинктивно ищут аналоги phpmyadmin, и натыкаются на phppgadmin. К сожалению, phppgadmin не развивается уже несколько лет, так что о мертвых или хорошо, или ничего. В общем, промолчим, пожалуй.

Не по всем из этих инструментов у меня есть опыт использования, поэтому прошу высказаться в комментариях. Что используете вы?

Также надо отметить, что на конференцию pgday приедут разработчики популярных инструментов не только для постгреса, но и других бд, можно будет их помучать вопросами и высказать какие-то пожелания по фичам. В любом случае, приглашаем всех посетить это полезнейшее мероприятие, которое пройдет в Санкт-Петербурге 5-7 июля!

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

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

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin'e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

1. Использование временных таблиц

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

Такие таблицы создаются как обычные, но с ключевым словом TEMP, и автоматически удаляются после завершения сессии.

Ключ ON COMMIT DROP автоматически удаляет таблицу (и все связанные с ней объекты) при завершении транзакции.

2. Часто используемый сокращенный синтаксис Postgres

можно записать менее громоздко:

Сокращенная запись конструкции (I)LIKE '%text%'

Поиск регулярными выражениями (имеет отличный от LIKE синтаксис)
оператор ~ (одна тильда) воспринимает регулярные выражения
оператор ~* (одна тильда и звездочка) регистронезависимая версия ~

Приведу пример поиска разными способами строк, которые содержат слово text

Cокращенный синтаксис	Описание	Аналог (I)LIKE
~ ‘text’ or ~~ ‘%text%’	Проверяет соответствие выражению с учётом регистра	LIKE '%text%'
~* ‘text’ ~~* ‘%text%’	Проверяет соответствие выражению без учёта регистра	ILIKE '%text%'
!~ ‘text’ !~~ ‘%text%’	Проверяет несоответствие выражению с учётом регистра	NOT LIKE '%text%'
!~* ‘text’ !~~* ‘%text%’	Проверяет несоответствие выражению без учёта регистра	NOT ILIKE '%text%'

3. Общие табличные выражения (CTE). Конструкция WITH

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

Примеры будут примитивны, чтобы уловить суть.

a) Простой SELECT

Таким способом можно 'оборачивать' какие-либо запросы (даже UPDATE, DELETE и INSERT, об этом будет ниже) и использовать их результаты в дальнейшем.

b) Можно создать несколько таблиц, перечисляя их нижеописанным способом

c) Можно даже вложить вышеуказанную конструкцию в еще один (и более) WITH

По производительности следует сказать, что не стоит помещать в секцию WITH данные, которые будут в значительной степени фильтроваться последующими внешними условиями (за пределами скобок запроса), ибо оптимизатор не сможет построить эффективный запрос. Удобнее всего положить в CTE результаты, к которым требуется несколько раз обращаться.

4. Функция array_agg(MyColumn).

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.
В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).
При использовании GROUP BY в массив попадут данные какого-либо столбца относительно каждой группы.

Сразу опишу еще одну функцию и перейдем к примеру.
array_to_string(array[], ';') позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах). В качестве разделителя можно использовать

Табуляция \t — к примеру, позволит при вставки ячейки в EXCEL без усилий разбить значения на столбцы (использовать так: array_to_string(array[], E'\t') )
Перевод строки \n — разложит значения массива по строкам в одной ячейке (использовать так: array_to_string(array[], E'\n') — объясню ниже почему)

Выдаст результат:

Выполним обратное действие. Разложим массив в строки при помощи функции UNNEST, заодно продемонстрирую конструкцию SELECT columns INTO table_name. Помещу это в спойлер, чтобы статья не сильно разбухала.

Результат:

5. Ключевое слово RETURNIG *

указанное после запросов INSERT, UPDATE или DELETE позволяет увидеть строки, которых коснулась модификация (обычно сервер сообщает лишь количество модифицированных строк).
Удобно в связке с BEGIN посмотреть на что именно повлияет запрос, в случае неуверенности в результате или для передачи каких либо id на следующий шаг.

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

Таким образом, выполнится удаление данных, и удаленные значения передадутся на следующий этап. Все зависит от вашей фантазии и целей. Перед применением сложных конструкций обязательно изучите документацию вашей версии СУБД! (при параллельном комбинировании INSERT, UPDATE или DELETE существуют тонкости)

6. Сохранение результата запроса в файл

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

7. Выполнение запроса на другой базе

Не так давно узнал, что можно адресовать запрос к другой базе, для этого есть функция dblink (все подробности в мануале)

Если возникает ошибка:

необходимо выполнить установку расширения следующей командой:

8. Функция similarity

Функция определения схожести одного значения к другому.

Использовал для сопоставления текстовых данных, которые были похожи, но не равны друг другу (имелись опечатки). Сэкономил уйму времени и нервов, сведя к минимуму ручную привязку.
similarity(a, b) выдает дробное число от 0 до 1, чем ближе к 1, тем точнее совпадение.
Перейдем к примеру. С помощью WITH организуем временную таблицу с вымышленными данными (и специально исковерканными для демонстрации функции), и будем сравнивать каждую строку с нашим текстом. В примере ниже будем искать то, что больше похоже на ООО «РОМАШКА» (подставим во второй параметр функции).

Получим следующий результат:

Если возникает ошибка

необходимо выполнить установку расширения следующей командой:

Получим такой результат:

Сортируем по similarity DESC. Первыми результатами видим наиболее похожие строки (1— полное сходство).

Необязательно выводить значение similarity в SELECT, можно просто использовать его в условии WHERE similarity(c_name, 'ООО «РОМАШКА»') >0.7
и самим задавать устраивающий нас параметр.

P.S. Буду признателен, если подскажете какие еще есть способы сопоставления текстовых данных. Пробовал убирать регулярными выражениями все кроме букв/цифр, и сопоставлять по равенству, но такой вариант не срабатывает, если присутствуют опечатки.

9. Оконные функции OVER() (PARTITION BY ORDER BY )

10. Множественный шаблон для LIKE

Задача. Необходимо отфильтровать список пользователей, имена которых должны соответствовать определенным шаблонам.

Как всегда, представлю простейший пример:

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

Продемонстрирую, как сделать его более компактным:

Можно проделать интересные трюки, используя подобный подход.
Напишите в комментариях, если есть мысли, как еще можно переписать исходный запрос.

11. Несколько полезных функций

NULLIF(a,b)
Возникают ситуации, когда определенное значение нужно трактовать как NULL.
Например, строки нулевой длины ( '' — пустые строки) или ноль(0).
Можно написать CASE, но лаконичнее использовать функцию NULLIF, которая имеет 2 параметра, при равенстве которых возвращается NULL, иначе выводит исходное значение.

COALESCE выбирает первое не NULL значение

GREATEST выбирает наибольшее значение из перечисленных

LEAST выбирает наименьшее значение из перечисленных

PG_TYPEOF показывает тип данных столбца

PG_CANCEL_BACKEND останавливаем нежелательные процессы в базе

Внимание! Ни в коем случае не убивайте зависший процесс через консоль KILL -9 или диспетчер задач.
Это может привести к краху БД, потере данных и долгому автоматическому восстановлению базы.

12. Экранирование символов

Начну с основ.
В SQL строковые значения обрамляются ' апострофом (одинарной кавычкой).
Числовые значения можно не обрамлять апострофами, а для разделения дробной части нужно использовать точку, т.к. запятая будет воспринята как разделитель

результат:

Все хорошо, до тех пор пока не требуется выводить сам знак апострофа '
Для этого существуют два способа экранирования (известных мне)

результат одинаковый:

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

получаю данные в первозданном виде:

Если этого мало, и внутри требуется использовать два символа доллара подряд $$, то Postgres позволяет задать свой «ограничитель». Стоит лишь между двумя долларами написать свой текст, например:

Увидим наш текст:

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

Заключение

Надеюсь, данный материал поможет узнать много нового начинающим и «средничкам». Сам я не являюсь разработчиком, а могу лишь назвать себя любителем SQL, поэтому то, как использовать описанные приемы — решать Вам.

Желаю успехов в изучении SQL. Жду комментариев и благодарю за прочтение!
UPD. Вышло продолжение

Что такое графический интерфейс PostgreSQL? Зачем он нужен? Как это может помочь вам в управлении базами данных? Узнайте о лучшем программном обеспечении Postgre GUI, которое можно попробовать в 2021 году.

Перевод публикуется с сокращениями, автор оригинальной статьи Ilon Adams.

PostgreSQL – это передовая открытая система управления объектно-реляционными базами данных. В основном она используется на предприятиях и поддерживает запросы SQL и JSON.

По данным Stack Overflow, PostgreSQL является второй наиболее используемой СУБД после MySQL в 2021 году. Более 40% из 70 000+ опрошенных предпочитают Postgres базам данных SQLite, MongoDB, Redis и другим.

У пользователя, есть два способа администрирования СУБД:

писать запросы через CLI (не всем это нравится);
использовать графический пользовательский интерфейс (GUI) Postgres.

Второй вариант намного удобнее, т. к. он позволяет повысить производительность. Давайте рассмотрим наиболее используемые инструменты GUI .

Что такое GUI PostgreSQL?

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

Основные причины, по которым пользователи предпочитают графический интерфейс:

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

Использование GUI дает следующие преимущества:

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

Лучшее программное обеспечение с графическим интерфейсом

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

Прежде всего есть низкоуровневый конструктор внутренних инструментов UI Bakery . Изначально он не был создан для управления Postgres, однако с его помощью вы можете подключить несколько источников данных (базы данных, сторонние приложения, REST API) в одном UI . Bakery обладает широкими возможностями визуализации данных для отображения PostgreSQL, MongoDB, MySQL, Microsoft SQL, Redis и т.д.

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

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

1. pgAdmin

pgAdmin – кроссплатформенный графический инструмент с открытым исходным кодом.

совместим с Linux, Windows, macOS;
позволяет работать с несколькими серверами одновременно;
экспорт в CSV;
планирование запросов;
возможность отслеживать ваши сеансы, блокировки БД с помощью панели мониторинга;
ярлыки в редакторе SQL для более удобной работы;
встроенный отладчик процедурного языка;
тщательная документация и активное сообщество.

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

2. DBeaver

DBeaver – инструмент управления PostgreSQL с открытым исходным кодом, поддерживающий коннект к нескольким базам данных.

кроссплатформенность;
поддержка более 80 баз данных;
визуальный конструктор, позволяющий добавлять запросы без навыков работы с SQL;
несколько представлений данных;
импорт/экспорт данных в CSV, HTML, XML, JSON, XLS, XLSX;
повышенная безопасность данных;
полнотекстовый поиск данных и возможность отображения результатов в виде таблиц/представлений;
доступен бесплатный тарифный план.

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

3. Navicat

Интуитивно понятный (с недавнего времени проприетарный) GUI для Postgres.

простая и быстрая установка;
поддержка Windows, Linux, iOS;
удобный визуальный конструктор SQL;
автодополнение кода;
инструмент моделирования данных: управление объектами базы данных, схемами проектирования;
планировщик заданий: запускайте задания, получайте уведомления о завершении задания;
синхронизация источников данных;
импорт/экспорт данных в Excel, Access, CSV и другие форматы;
защита данных с помощью SSH и SSL;
использование облачных сервисов Amazon, Google и др.

низкая производительность GUI;
высокая цена по сравнению с конкурентами;
одна лицензия ограничена одной платформой (вам понадобятся 2 отдельные лицензии для PostgreSQL и MySQL);
множество дополнительных возможностей, требующих времени для изучения.

4. DataGrip

Продвинутая IDE для работы с несколькими базами данных, созданная в JetBrains .

кроссплатформенность (поддержка Windows, macOS, Linux);
простая навигация по схеме;
настраиваемый UI с консолью для обеспечения безопасности выполняемой работы;
быстрое обнаружение ошибок;
встроенная система контроля версий;
поддержка MySQL, SQLite, MariaDB, Cassandra и других;
отчеты с возможностью их интеграции с диаграммами и графиками;
автодополнение кода.

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

5. HeidiSQL

Инструмент с GUI и открытым исходным кодом для Postgres (и не только). Пока поддерживается только Windows .

простая установка, легковесная по сравнению с конкурентами;
поддержка PostgreSQL, MySQL, Microsoft SQL Server, MariaDB;
возможность подключения и управления несколькими серверами баз данных в одном окне;
прямой экспорт SQL из одной базы данных в другую;
массовый просмотр и редактирование таблиц;
автодополнение кода и подсветка синтаксиса;
сообщество с активной поддержкой и регулярные обновы;
экспорт таблиц и данных в Excel, HTML, JSON, PHP;
зашифрованное соединение.

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

6. TablePlus

Программное обеспечение с графическим интерфейсом для управления базами данных SQL и NoSQL. С закрытым исходным кодом.

высокая производительность;
настраиваемый UI;
подсветка синтаксиса;
высокий уровень безопасности данных обеспечивается за счет сквозного шифрования в соединении.

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

7. OmniDB

Простой открытый инструмент с GUI для PostgreSQL.

кроссплатформенность (поддержка Windows, Linux, macOS);
поддержка PostgreSQL, Oracle, MySQL, MariaDB;
очень отзывчивый и легкий по сравнению с некоторыми альтернативами;
автозаполнение SQL;
подсветка синтаксиса;
возможность создания настраиваемых диаграммы для отображения релевантных метрик БД;
встроенная отладка.

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

Заключение: UI Bakery – неочевидный, но мощный вариант

Когда вы выбираете программное обеспечение с GUI , основывайте окончательное решение на нескольких аспектах:

размер команды;
используемые ОС;
тип СУБД;
количество баз данных, с которыми вы планируете работать.

DBeaver, DataGr i p и HeidiSQL больше подходят для одного человека, работающего с одной базой. Navicat – выбор для команды благодаря возможности совместной работы. Почти все упомянутые инструменты являются кроссплатформенными за исключением HeidiSQL, который поддерживает только Windows.

Низкоуровневая UI Bakery отлично подходит, если вам нужно объединить несколько различных источников данных – будь то базы данных, сторонние инструменты или API.

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

Пошаговая инструкция по установке, настройке и наполнению базы данных PostgreSQL с помощью pgAdmin и SQL-запросов.

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) – достаточно распространенный SQL диалект. Для этого откроем страницу загрузки , выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки , выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server . На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home → Databases (1) → postgres и далее выбираем Query Tool .

Начнем с создания таблицы классов ( classrooms ). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса ( query tool ) и запустите ( run или F5 ).

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms , если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение ( constraint ) в конце строки добавлен оператор CASCADE . CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом ( primary key ), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY , этот приём является альтернативой синтаксису последовательности ( CREATE SEQUENCE ). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу ( alter table ).

Теперь давайте создадим таблицу учеников ( students ). Новая таблица будет содержать: уникальный идентификатор ( id ), имя ученика ( name ), и внешний ключ ( foreign key ), который будет указывать ( references ) на таблицу классов.

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id , которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса ( classroom_id ), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов ( classrooms ).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников ( students ). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников ( students ), в которой указан идентификатор класса ( classroom_id ), не существующий в таблице classrooms . Например: у нас в таблице классов 10 записей ( id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с >

Теперь давайте добавим немного данных в таблицу классов ( classrooms ). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса ( classroom_id ) передать null . Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий ( assignments ), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок ( grades ), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data ).

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

Отлично! Мы установили, настроили и наполнили базу данных.

Итак, в этой статье мы научились:

создавать базу данных;
создавать таблицы;
наполнять таблицы данными;
устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц ( JOIN ) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

Читайте также: