13.10.2020 16:00
vlstrochkov 7 10500 Источник

Привет, Хабр! Приглашаем на бесплатный 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», «Нереляционные базы данных».