SQL-Ex blog

Вместо этого для этой серии я просто предполагаю, что мог бы написать более одной статьи относительно запросов данных в PostgreSQL. Но для этой вводной части я собираюсь сосредоточиться на основной модели поведения SELECT, FROM и WHERE с упором на отличиях от SQL Server. Это не будет фундаментальным трудом о том, как извлекать данные в PostgreSQL, но исследованием тех моментов, с которым вы, вероятно, столкнетесь, имея уже знания того, как, на ваш взгляд, это должно работать. И да, там есть кое-что забавное. Давайте начнем.

В тестовой базе данных, которую я использую для этой серии, я создал пару схем и таблиц в этих схемах. Если вы хотите выполнять код или посмотреть на структуры данных, код находится в репозитории ScaryDBA/LearningPostgreSQL. Объекты и база данных, которые вам потребуются, можно создать/восстановить, используя скрипт CreateDatabase.sql, с последующим добавлением тестовых данных с помощью скрипта SampleData.sql. Остальной код из этой статьи находится в папке 10_Select.

FROM

Мне действительно нравится то, как документация PostgreSQL определяет, что вы делаете в предложении FROM:

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

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

Результат списка в FROM представляет собой промежуточную виртуальную таблицу, которая затем может подвергнуться трансформации в предложениях WHERE, GROUP BY и HAVING и есть, наконец, результат всего табличного выражения.

Это звучит так, как будто предложение FROM есть временная таблица или что-то в этом роде. Да, я знаю (читая далее в документации), что планы выполнения в PostgreSQL похожи на планы SQL Server, и это описание ведет нас по пути: виртуальная таблица, это не точно, в зависимости от всего запроса, привлеченных структур и статистики. Хотя это может помочь с визуализацией, оно, конечно, так не реализовано.

Однако остальное соответствует моим ожиданиям. Перечислите имена таблиц и/или определите таблицу посредством подзапроса SELECT. Присвоение алиасов во многом работает так же в T-SQL, поскольку это способ, который указан в Стандарте ANSI SQL, которому PostgreSQL следует весьма близко.

JOIN

Многое в предложении FROM PGSQL аналогично T-SQL. Первое большое отличие - в использовании операций JOIN. Стандартные операции соединения те же самые: INNER, LEFT/RIGHT OUTER, CROSS, FULL OUTER. Они все выполняют те же самые логические функции. Есть отличие во внутреннем синтаксисе. Таково предложение USING:

SELECT

r.radio_name,

m.manufacturer_name 

FROM

radio.radios r

JOIN radio.manufacturers m

USING (manufacturer_id);

Впервые, когда я сделал это, и оно заработало, мне показалось это черной магией. Конечно, для настоящей магии я должен был использовать предложение NATURAL:

SELECT

r.radio_name,

m.manufacturer_name 

FROM

radio.radios r

NATURAL JOIN radio.manufacturers m;

По сути, PostgreSQL делает это, основываясь на именах и типах данных, которые должны быть общими столбцами у этих двух таблиц, поэтому вам вообще не требуется определять критерии JOIN. Обратите также внимание, что я опустил синтаксический сахар INNER. PostgreSQL выяснил, что мне требуется точно так же, как это делает SQL Server. Мне нравится этот стандарт, который позволяет мне так действовать:

SELECT

a.antenna_name,

ab.band_id

FROM

radio.antenna a

NATURAL LEFT JOIN radio.antennabands ab;

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

Естественные соединения прекрасная вещь, но остерегайтесь того, что если стандарт именования позволяет вам иметь одинаковые имена в разных таблицах (например, столбец Name или Description), вы можете получить неожиданные результаты. Важно также отметить, что если таблицы не содержат столбцов с совпадающими именами, вы получите те же результаты, что и при использовании CROSS JOIN. Следовательно, это то, что я бы редко использовал в промышленном коде, но было бы приятно делать в запросах ad hoc.

LATERAL

Помимо подзапросов, имеются также табличнозначные функции. Есть некоторые различия в том, как они работают, но дьявол кроется в деталях, а не в общем поведении. При использовании подзапросов и функций вы можете получить эквивалент CROSS APPLY в T-SQL при помощи версии PostgreSQL LATERAL.

SELECT

b.band_name,

rl.radio_name

FROM

radio.bands b,

LATERAL (

SELECT

*

FROM

radio.radios r

JOIN radio.radiobands rb

ON

r.radio_id = rb.radio_id

WHERE

b.frequency_start_khz < 146) rl;

Подобно CROSS APPLY в T-SQL, вы в основе своей получаете функциональный цикл. Я имею в виду то, что для каждой строки в таблице radio.bands вы получите выполнение запроса LATERAL. Любые столбцы, на которые из внешних таблиц (в данном случае radio.bands) имеются ссылки, будут доступны в качестве параметров в запросе.

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

WHERE

Если не принимать в расчет уникальные функции PostgreSQL в предложении WHERE, то в целом это то же самое, что и в T-SQL. Нет ничего странного и в функциональности. Например, вы можете использовать предложение WHERE для определения критерия соединения вместо использования ON. Однако, как и в T-SQL, если вы делаете это с OUTER JOIN, то эффективно превращаете его в INNER JOIN (да, я знаю, что это не вся история, но в целом это то, что происходит).

Имейте в виду, что само количество выражений для вычисления значения, а также функций и операторов устрашает. Я не собираюсь пытаться объяснить даже частичный список возможностей. Достаточно сказать, что вы можете делать массу вещей в предложении WHERE в PostgreSQL, которые просто недоступны в SQL Server. Например, у нас есть оператор LIKE для поиска строки с похожими значениями. В PostgreSQL вы также получаете SIMILAR TO <регулярное выражение> и POSIX <регулярное выражение>.

SELECT

Я выбрал порядок FROM, WHERE, SELECT, поскольку PostgreSQL, как и SQL Server, именно в таком порядке выполняет операции. В простом запросе, вы сначала определяете, откуда берутся данные: FROM. Затем вы применяете фильтры к этим данным: WHERE. Наконец, вы решаете, что должно возвращаться: SELECT.

Все примеры, которые приводил до сих пор, демонстрировали основы оператора SELECT. Это очень близко к T-SQL. Я могу указать столбцы из определенных таблиц и использовать алиасы этих таблиц, чтобы сделать код чище (и короче). Фактически, в том, что я использовал, я могу увидеть: DISTINCT, WITH, UNION, ORDER BY т.д.

Однако есть некоторое действительно интересное поведение. Например, если вы опустите алиас столбца, как здесь:

SELECT

rb.radio_id + rb.band_id

FROM

radio.radiobands rb;

Есть также несколько интересных функций и предложений, которые меняют поведение запросов. Например, LIMIT работает очень похоже на TOP:

SELECT

r.radio_name

FROM

radio.radios r

LIMIT 3;

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

SELECT

r.radio_name

FROM

radio.radios r

LIMIT 3 OFFSET 2;

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

Другая вещь - FETCH. Он работает подобно LIMIT (в SQL Server имеется OFFSET - часть предложения ORDER BY и FETCH, но синтаксис немного отличается):

SELECT

r.radio_name

FROM

radio.radios r

ORDER BY

r.radio_name DESC 

FETCH NEXT 3 ROWS ONLY;

Я могу использовать OFFSET в той же манере. Однако имеется также дополнительное поведение. Вместо ONLY я могу сказать ему WITH TIES. В этом случае, если более одного значения удовлетворяет критерию первых трех, мы получим любые связанные значения, т.е. возможно более 3 строк, в зависимости от того, сколько имеется соответствующих строк.

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

Заключение

По большей части при запросах к PostgreSQL я просто начинаю печатать, и это, как правило, работает. Действительно имеется больше общего, чем различий. Однако ценно отметить эти различия, особенно когда приходится использовать что-то типа TOP или LIMIT/FETCH. В целом, это мелочи, но они послужат подсказкой, когда вы будете писать свой код.

Ссылки по теме
1. Оператор SELECT
2. Постраничная разбивка записей (пейджинг)