Привет, Хабр! Приглашаем на бесплатный Demo-урок «Параллельный кластер CockroachDB», который пройдёт в рамках курса «PostgreSQL». Также публикуем перевод статьи Тома Брауна — Principal Systems Engineer at EnterpriseDB.
В этой статье рассмотрим несколько полезных советов по работе с PostgreSQL:
Ссылка на всю строку целиком
Сравнение нескольких столбцов
Общие табличные выражения
Пользовательские параметры конфигурации
Сравнение логических значений без "равно"
Изменение типа столбца без лишних затрат
Информация о секции, в которой находится строка
Таблицы — это типы
Ссылка на всю строку целиком
Вы когда-нибудь пробовали выполнить подобный запрос?
SELECT my_table FROM my_table;Запрос выглядит странно. Он возвращает все столбцы таблицы в виде одного столбца. Зачем это может понадобиться? Что ж, я думаю, вы не раз ссылались на таблицы следующим образом:
SELECT table_a.column, table_b.column
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.aid;Здесь есть ссылка на строку, но только один столбец. И здесь ничего необычного нет. А как насчет этого?
SELECT data, backup_data
FROM data
FULL JOIN backup_data ON data = backup_data
WHERE data IS NULL OR backup_data IS NULL;Здесь есть таблица data и ее резервная копия backup_data. И что, если мы хотим увидеть разницу между ними: найти изменения с момента резервного копирования и узнать не потеряли ли мы какие-то строки в резервной копии?
Для демонстрации создадим таблицу и вставим три строки:
postgres=# CREATE TABLE data (id serial, person text, country text);
CREATE TABLE
postgres=# INSERT INTO data (person, country)
VALUES ('Tim','France'),('Dieter','Germany'),('Marcus','Finland');
INSERT 0 3Теперь создадим идентичную копию таблицы и скопируем в нее данные:
postgres=# CREATE TABLE backup_data (id serial, person text, country text);
CREATE TABLE
postgres=# INSERT INTO backup_data SELECT * FROM data;
INSERT 0 3Мы хотим, чтобы таблицы были разными, поэтому одну строку удалим и одну строку добавим:
postgres=# DELETE FROM data WHERE id = 2;
DELETE 1
postgres=# INSERT INTO data (person, country)
VALUES ('Roberto','Italy');
INSERT 0 1Давайте посмотрим, что произойдет, если мы запустим запрос для сравнения таблиц:
postgres=# SELECT data, backup_data
postgres-# FROM data
postgres-# FULL JOIN backup_data ON data = backup_data
postgres-# WHERE data IS NULL OR backup_data IS NULL;
data | backup_data
-------------------+--------------------
| (2,Dieter,Germany)
(4,Roberto,Italy) |
(2 rows)Мы видим, что таблица backup_data содержит строку, которая отсутствует в таблице data, и наоборот.
Можно использовать эту возможность и так:
postgres=# SELECT to_jsonb(data) FROM data;
to_jsonb
-----------------------------------------------------
{"id": 1, "person": "Tim", "country": "France"}
{"id": 3, "person": "Marcus", "country": "Finland"}
{"id": 4, "person": "Roberto", "country": "Italy"}
(3 rows)Мы превратили все наши данные в JSON!
Сравнение нескольких столбцов
Это очень интересный трюк, с помощью которого можно сделать запросы короче и читабельнее.
Допустим, у нас есть следующий запрос:
SELECT country, company, department
FROM suppliers
WHERE country = 'Australia'
AND company = 'Skynet'
AND department = 'Robotics';Мы можем избавиться от AND:
SELECT country, company, department
FROM suppliers
WHERE (country, company, department) = ('Australia','Skynet','Robotics');И также можем использовать IN для условий OR:
SELECT country, company, department
FROM suppliers
WHERE department = 'Robotics'
AND (
(country = 'Australia'
AND company = 'Skynet')
OR
(country = 'Norway'
AND company = 'Nortech')
);Этот запрос можно сократить:
SELECT country, company, department
FROM suppliers
WHERE department = 'Robotics'
AND (country, company) IN (('Australia','Skynet'),('Norway','Nortech'));Общие табличные выражения
Допустим, у вас есть доступ к базе данных только для чтения и вы не можете создавать таблицы. И также у вас есть небольшой набор данных, который вы бы хотели соединить (join) с существующими таблицами.
SELECT station, time_recorded, temperature
FROM weather_stations;
station | time_recorded | temperature
----------------+---------------------+-------------
Biggin_Hill_14 | 2020-02-02 13:02:44 | 22.4
Reigate_03 | 2020-02-02 16:05:12 | 20.9
Aberdeen_06 | 2020-02-02 15:52:49 | 8.5
Madrid_05 | 2020-02-02 14:05:27 | 30.1
(4 rows)Допустим, вы х��тите получить представление о том, насколько тепло или холодно на каждой из станций. Тогда можно сделать примерно такой запрос:
SELECT station,
CASE
WHEN temperature <= 0 THEN 'freezing'
WHEN temperature < 10 THEN 'cold'
WHEN temperature < 18 THEN 'mild'
WHEN temperature < 30 THEN 'warm'
WHEN temperature < 36 THEN 'hot'
WHEN temperature >= 36 THEN 'scorching'
END AS temp_feels
FROM weather_stations;В данном способе неудобно добавлять условия. Это можно упростить, создав псевдо-таблицу с использованием общих табличных выражений (CTE, common table expression):
WITH temp_ranges (temp_range, feeling, colour) AS (
VALUES
('(,0]'::numrange, 'freezing', 'blue'),
('( 0,10)'::numrange, 'cold', 'white'),
('[10,18)'::numrange, 'mild', 'yellow'),
('[18,30)'::numrange, 'warm', 'orange'),
('[30,36)'::numrange, 'hot', 'red'),
('[36,)'::numrange, 'scorching', 'black')
)
SELECT ws.station, tr.feeling, tr.colour
FROM weather_stations ws
INNER JOIN temp_ranges tr ON ws.temperature <@ tr.temp_range;Ес��и вы не знакомы с диапазонными типами, то вас могут смутить "numrange". Это один из диапазонных типов, предназначенный для представления диапазона чисел. Круглые скобки означают исключение границы, квадратные — включение. Таким образом, '(0,10]' означает "от 0, но не включая 0, и до 10 включительно". Отсутствующая левая граница диапазона означает все значения меньше указанного числа, а отсутствующая правая — больше указанного числа.
Пользовательские параметры конфигурации
Postgres поставляется с большим набором параметров, которые позволяют настраивать все аспекты системы базы данных, но вы также можете добавлять свои параметры и называть их так, как вам удобно, при условии, что вы укажите префикс.
Например, можно добавить в postgresql.conf следующий параметр:
config.cluster_type = 'staging'А затем получить его значение с помощью команды SHOW.
postgres=# SHOW config.cluster_type;
config.cluster_type
---------------------
staging
(1 row)Обратите внимание, что эти параметры не отображаются в каталоге pgsettings и не выводятся командой SHOW ALL.
Так почему мы должны обязательно указывать префикс конфигурации? До PostgreSQL 9.2 был параметр customvariable_classes, который принимал список классов, которые могли использоваться расширениями для их собственных параметров. Вам нужно было добавить класс расширения в этот список, если вы хотели настроить его через postgresql.conf. Однако это требование было убрано в более поздних версиях, и вам больше не нужно объявлять их явно. Встроенные параметры не имеют префиксов, поэтому любые пользовательские параметры должны иметь префиксы, иначе они не ��удут приниматься.
Такие параметры удобно использовать для предоставления метаданных о кластере.
Сравнение логических значений без "равно"
Вероятно, вы писали подобные запросы:
SELECT user, location, active
FROM subscriptions
WHERE active = true;Знаете ли вы, что вам не нужно писать "= true"? Можно упростить:
WHERE activeЭто работает, потому что булевы значения не нужно сравнивать с другим булевым значением, поскольку выражения в любом случае возвращают true или false. Отрицание можно написать так:
WHERE NOT activeЭто тоже читается лучше.
Изменение типа столбца без лишних затрат
Часто при изменении типа столбца в таблице с данными, необходимо пересоздавать всю таблицу. Но во многих случаях этого не происходит.
И мы можем найти такие типы:
SELECT
castsource::regtype::text,
array_agg(casttarget::regtype ORDER BY casttarget::regtype::text) casttargets
FROM pg_cast
WHERE castmethod = 'b'
GROUP BY 1
ORDER BY 1;Этот запрос вернет относительно небольшой список типов с информацией о том, в какие "бинарно совместимые" типы они могут быть преобразованы. Из результатов вы увидите, что типы text, xml, char и varchar являются взаимозаменяемыми. Поэтому если у вас есть таблица, содержащая XML-данные в столбце text, не бойтесь его преобразовывать (обратите внимание, что при невалидном XML Postgres возвратит ошибку).
Информация о секции, в которой находится строка
Таблица может быть секционирована и вам может понадобиться узнать, в какой секции находится строка? Это легко: просто добавьте tableoid :: regclass в SELECT. Например:
postgres=# SELECT tableoid::regclass, * FROM customers;
tableoid | id | name | country | subscribed
--------------+-----+----------------+----------------+------------
customers_de | 23 | Hilda Schumer | Germany | t
customers_uk | 432 | Geoff Branshaw | United Kingdom | t
customers_us | 815 | Brad Moony | USA | t
(3 rows)Здесь tableoid - это скрытый системный столбец, который просто нужно явно указать в SELECT. Он возвращает OID (Object Identifier) таблицы, к которой принадлежит строка. Если вы приведете его к типу regclass, то получите имя таблицы.
Таблицы — это типы
Да, вы все правильно услышали. Каждый раз, когда вы создаете таблицу, вы фактически также создаете новый тип. Смотрите:
CREATE TABLE books (isbn text, title text, rrp numeric(10,2));Мы можем использовать этот тип таблицы при создании другой таблицы, как параметр функции или в качестве возвращаемого типа:
CREATE TABLE personal_favourites (book books, movie movies, song songs);Затем можно вставить данные:
INSERT INTO personal_favourites (book)
VALUES (('0756404746','The Name of the Wind',9.99));Чтобы получить отдельные столбцы из табличного значения, можно выбрать столбец из столбца:
SELECT (book).isbn, (book).title, (book).rrp
FROM personal_favourites;Теперь я знаю, о чем вы думаете: таблица, содержит табличный тип, который содержит типы, также является типом? Да, но давайте не будем углубляться в эти детали, иначе мы окажемся в запутанной ситуации в Inception-стиле.
И как я уже упоминал в "Ссылка на всю строку целиком", вы можете преобразовать всю строку в JSON:
postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;
jsonb_pretty
----------------------------------------------
{ +
"book": { +
"rrp": 9.99, +
"isbn": "0756404746", +
"title": "The Name of the Wind" +
}, +
"song": { +
"album": "Grace", +
"title": "This is our Last Goodbye",+
"artist": "Jeff Buckley" +
}, +
"movie": { +
"title": "Magnolia", +
"studio": "New Line Cinema", +
"release_date": "2000-03-24" +
} +
}Эту функциональность можно использовать для создания схем с данными в виде JSON, чтобы получить NoSQL-подобную функциональность, но с данными, имеющими определенную структуру.
Но подождите, а что, если я хочу хранить и запрашивать все мои любимые книги, песни и фильмы, а не только одну запись?
Это тоже работает. Любой тип, в том числе и табличный, можно превратить в массив, добавив [] после имени типа данных. Вместо того чтобы заново создавать таблицу, давайте просто преобразуем столбцы массивы, а затем добавим еще одну книгу:
ALTER TABLE personal_favourites
ALTER COLUMN book TYPE books[] USING ARRAY[book];
ALTER TABLE personal_favourites
ALTER COLUMN movie TYPE movies[] USING ARRAY[movie];
ALTER TABLE personal_favourites
ALTER COLUMN song TYPE songs[] USING ARRAY[song];Добавляем еще одну книгу:
UPDATE personal_favourites
SET book = book || ('1408891468','Jonathan Strange and Mr Norrell',7.99)::books;Теперь результат выглядит следующим образом:
postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites))
FROM personal_favourites;
jsonb_pretty
--------------------------------------------------------
{ +
"book": [ +
{ +
"rrp": 9.99, +
"isbn": "0756404746", +
"title": "The Name of the Wind" +
}, +
{ +
"rrp": 7.99, +
"isbn": "1408891468", +
"title": "Jonathan Strange and Mr Norrell"+
} +
], +
"song": [ +
{ +
"album": "Grace", +
"title": "This is our Last Goodbye", +
"artist": "Jeff Buckley" +
} +
], +
"movie": [ +
{ +
"title": "Magnolia", +
"studio": "New Line Cinema", +
"release_date": "2000-03-24" +
} +
] +
}Теперь запрос выводит массив книг и это без каких-либо его изменений. Надеюсь, эти советы помогут вам использовать Postgres более эффективно! Чтобы узнать больше, ознакомьтесь с нашей новой электронной книгой 5 Ways to Get More from PostgreSQL (Пять способов получить больше от PostgreSQL).
Интересно развиваться в данном направлении? Запишитесь на бесплатный Demo-урок «Секционирование таблиц PostgreSQL» и участвуйте в онлайн-встрече с Евгением Аристовым — руководителем образовательной программы «PostgreSQL» и курсов «Базы данных», «Software Architect», «MS SQL Server Developer», «Нереляционные базы данных».
