Привет, Хабр! Приглашаем вас на бесплатный демонстрационный урок «Параллельный кластер CockroachDB» , который пройдет в рамках курса PostgreSQL. Также мы публикуем перевод статьи Тома Брауна — главного системного инженера EnterpriseDB.Теги: #Администрирование базы данных #postgresql #sql #Ссылки на всю строку #Сравнение выбранных столбцов #Жестко запрограммированные таблицы #Пользовательские параметры конфигурации #Булевые значения могут быть автономными #Бесплатное преобразование типов данных столбцов #Таблицы являются типами
В этой статье мы рассмотрим несколько полезных советов по работе с 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;
Есть таблица данных и ее резервная копия 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 содержит строку, которой нет в таблице данных, и наоборот. Вы можете использовать эту функцию следующим образом: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 для условий ИЛИ: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'));
Общие табличные выражения
Допустим, у вас есть доступ к базе данных только для чтения и вы не можете создавать таблицы.Также у вас есть небольшой набор данных, которые вы хотели бы объединить с существующими таблицами.
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):
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;
Если вы не знакомы с типами диапазонов, вас может смутить «диапазон чисел».Это один из типов диапазона, предназначенный для представления диапазона чисел.
Круглые скобки означают исключение, квадратные скобки означают включение.
Таким образом, '(0,10]' означает "от 0, но не включая 0, и до 10 включительно".
Отсутствующий левый конец диапазона означает все значения меньше указанного числа, а отсутствующий правый конец означает все значения, большие указанного числа.
Пользовательские параметры конфигурации
Postgres поставляется с большим набором параметров, которые позволяют настраивать все аспекты системы базы данных, но вы также можете добавлять свои собственные параметры и называть их по своему усмотрению, при условии, что вы укажете префикс.Например, вы можете добавить в postgresql.conf следующий параметр:
config.cluster_type = 'staging'
А затем получите его значение с помощью команды SHOW.postgres=# SHOW config.cluster_type; config.cluster_type --------------------- staging (1 row)
Обратите внимание, что эти параметры не отображаются в каталоге pg. настройки и не отображаются командой ПОКАЗАТЬ ВСЕ.Так почему же нам нужно указывать префикс конфигурации? До PostgreSQL 9.2 существовал собственный параметр.
переменные_классы, которые принимали список классов, которые расширения могли использовать в качестве своих параметров.
Вам нужно было добавить класс расширения в этот список, если вы хотите настроить его через 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-данные в текстовом столбце, не бойтесь ее конвертировать (обратите внимание, что Postgres вернет ошибку, если XML недействителен).
Информация о разделе, в котором находится строка
Таблица может быть секционирована, и вам может потребоваться узнать, в каком разделе находится строка? Это просто: просто добавьте 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 (идентификатор объекта) таблицы, которой принадлежит строка.Если вы приведете его к 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;
Теперь я знаю, о чем вы думаете: является ли таблица, содержащая тип таблицы, содержащей типы, также типом? Да, но давайте не будем вдаваться в эти детали, иначе у нас будет неразбериха в стиле «Начало».И, как я упоминал в разделе «Ссылка на всю строку», вы можете преобразовать всю строку в JSON:
postgres=# SELECT jsonb_pretty(to_jsonb(personal_favourites)) FROM personal_favourites;
-
Работа С Сообщениями Об Ошибках — Git
19 Oct, 24 -
Разработка Windows 8 Началась
19 Oct, 24 -
Jre 6.0, Обновление 17
19 Oct, 24
