PostgreSQL

При помощи функции «row()» мы можем создавать записи — данные с типом record. -- Создаём данные с типом record select row(77, 99); -- Обращение к первому элементу select (row(77, 99)).f1; --77 -- Обращение ко второму элементу select (row(77, 99)).f2; --99   Для нескольких записей с типом record ситуация будет точно […]

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; -- нужно оставить в массиве объекты где у ключа "q" значение "true" select jsonb_path_query_array('[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb, '$[*] ? (@.q == true)'); Используем функцию «jsonb_path_query_array()«, которая умеет отбирать нужные объекты JSONB-массива по […]

Функция «trim_scale()» умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37 select trim_scale(42.150000000); -- 42.15

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в один JSONB объект с общим набором свойств (пар «ключ/значение»). Наш идеальный сценарий — все ключи в объектах будут уникальными. Просто подходит под задачу по «сливу» всех ключей в один JSONB объект. Решение: -- explain analyze […]

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто сгруппировать значения полей по 10 записей или по 1000. При этом все значения могут быть уникальными. Как это сделать? select array_agg(userid) as usersArray, string_agg(userid::text, '; ') as usersText from ( select U.userid, U.userid/10*10 as grouprank […]

Базовая установка PostgreSQL не даёт нам информацию об активности работы наших функций так как мониторинг выключен. Мониторинг требует фиксации данных, а это всегда небольшая дополнительная нагрузка на систему. Зачем нагружать то, что хорошо работает (до поры, до времени). И всё же, как посмотреть/активировать статистику работы своих функций? Сперва проверяем результат […]

Иногда нужно сортировать записи таблицы с некоторым собственным приоритетом. Под словом «некоторым» имеется ввиду несколько конкретных уникальных значений столбца, а не все возможные уникальные значения этого столбца. Хотя вы можете настроить сортировку по всем уникальным значениям. Мы рассмотрим решение, в котором исходная таблица будет иметь 7 уникальных значений, но они […]

После того как вы создали GIN индекс для текстового столбца таблицы, возникает вопрос о его использовании. CREATE INDEX efix_t_path_path_gin_ops ON a.t_path USING gin (path gin_trgm_ops) Какие именно операторы и выражения приводят к обращению к этому индексу, а не к какому-то другому? Возможные варианты обращения в предложении WHERE команды SELECT: like […]

Нам нужно привести целое число к строке с разделителями в виде пробелов между каждым тысячным разрядом, так чтобы от правого края числа, цифры отделялись по три символа. select regexp_replace(22333444::text, '(?=(?:.{3})+$)', ' ', 'g') -- 22 333 444 Используем регулярное выражение с захватами»(?=(?:.{3})+$)» и функцию в PostgreSQL «regexp_replace()».

Разворачиваем JSONB-массив чисел в таблицу при помощи функции «jsonb_array_elements()«. Через функцию агрегации «array_agg()» собираем числа из столбца таблицы в один SQL-массив. select array_agg(T.i) from (select jsonb_array_elements('[333,555]'::jsonb)::int8) as T(i); Для приведения к числу используем «::int8«.  

Иногда нужно получать более точные значения времени в процессе выполнения функции. Стандартное использование функции «now()» даёт не подходящее значение (только потому, что это значение одинаковое внутри тела функции где бы не вызвали). Для действительно уникальных значений времени (временной метки) нужно использовать функцию «clock_timestamp()«. Она возвращает фактическое текущее время, так что […]

У нас имеется JSONB-объект: select '{"arrjs": []}'::jsonb; Мы хотим наполнить пустой массив, который хранится под ключом «arrjs«. Будем наполнять простыми объектами. Как это сделать? Для примера будем использовать команду «DO«, чтобы имитировать работу функции. Внутри мы объявим наш объект и наполним его обычным диапазональным циклом в значениях от 0 до […]

Через функцию «md5()» и «random()» select md5(random()::text); -- aa44c7d2f833d47102f8e800af754074 Всегда получаем длину строки 32 символа. Есть цифры, есть латинские буквы в нижнем регистре. Функция random() сильно ограничивает количество возможных комбинаций так как по факту в строке остаются только цифры. Количество уникальных значений можно увеличить при помощи конкатенаций вида: select md5(random()::text […]

Ошибка информирует о том, что функция similarity(unknown, unknown) не существует в конкретной базе данных PostgreSQL и возможно в конкретной схеме данных у этой базы данных. Как правило функция «similarity()» принадлежит расширению «pg_trgm«. Скорее всего данное расширение не установлено в вашей базе данных. Но может быть так, что ваш проект использует […]

Команда: CREATE EXTENSION pg_trgm; После исполнения данной команды в текущую базу данных будет загружено новое расширение. Расширение с таким именем не должно быть уже загружено ранее. Если расширение уже будет установлено, тогда мы получим ошибку. Чтобы заменить ошибку на уведомление можно использовать предложение «IF NOT EXISTS»: CREATE EXTENSION IF NOT […]

Нам нужно получить логическое значение при попытке приведения строки SQL к целому числу SQL. Как это сделать? Можно использовать тильду «~» от регулярных выражений в стиле POSIX: select 'qwe' ~ '^[1-9][0-9]*$'; --false select '0000asd' ~ '^[1-9][0-9]*$'; --false select '0000' ~ '^[1-9][0-9]*$'; --false select '0123' ~ '^[1-9][0-9]*$'; --false select '123zxc' ~ […]

Существует несколько способов присваивания значений в свойствах JSONB-объектов в PostgreSQL: Квадратные скобки и строка Функция jsonb_set() Конкатенация с другим JSONB объектом Конкатенация с результатом функции jsonb_build_object() Для примера работы присваивания я буду использовать команду «DO». Визуальный вывод буду делать в журнал при помощи выражения «raise notice». Выполнять запросы буду в […]

Пример получения даты со временем в строковом виде: select to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); Мы используем функцию «to_char()» чтобы произвести преобразование типа «timestamp» в тип «text«.   Информационные ссылки Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Казалось бы такое простое действие, но когда это касается PostgreSQL то сразу всё становится сложно. Тупизна заключается в том, что в отличии от классических массивов в JavaScript, массивы PostgreSQL могут иметь понятие «размерности». Проблема преобразований может выстрелить, когда вы будете писать свои собственные функции, которые на входе будут принимать JSONB […]

Как получить последние 30 дат (более старых) относительно текущей даты в PostgreSQL? Для этого нам нужно воспользоваться встроенной функцией generate_series(), которая может принимать 3 параметра: Начальную дату Конечную дату Интервал (временной шаг) Пример SQL-запроса: SELECT generate_series( current_timestamp - INTERVAL '30D', current_timestamp, '1D' )::date; Данная функция возвращает нам табличный вид с […]

Как сгенерировать даты по диапазону в PostgreSQL? У нас есть дата с типом «timestamp with time zone«: SELECT '2022-12-12 15:04:06.180197+03'; У нас есть интервал: SELECT INTERVAL '15D'; Мы хотим сгенерировать все возможные даты по этим двум компонентам, зная интервал и одну дату. Как это сделать? SELECT generate_series( '2022-12-12 15:04:06.180197+03'::date, ('2022-12-12 […]

Даже если вы не создавали триггеры в таблицах, то «под капотом» при объявлении ограничений таблицы по внешним ключам они могут создаться автоматически без вашего ведома. Считайте их «служебными триггерами», а не вашими собственными. Проблема тут заключается в том, что если вы использовали технологию каскадного удаления между таблицами, то при переименовании […]

Если в базе данных много функций, то однажды в них будет сложно искать информацию. Речь идёт о составе исходных кодов самих функций. Например, нас может интересовать место (или места) где в функции делается вставка в определённую таблицу или где идёт обновление значений какого-нибудь столбца таблицы. Для поиска по исходным кодам: […]

В PostgreSQL интервалы можно умножать на целые числа при помощи обычного символа звёздочки. select 5 * '1 minute'::interval; Сначала нам нужно получить интервал одной минуты. Для этого мы создаём строку ‘1 minute’ и приводим её к типу interval. После этого мы можем умножать наше целое число на наш интервал одной […]

Иногда нужно не просто считать сумму всех элементов столбца, а считать с приращением следующего и предыдущего значения суммирования. Это из категории задач на смещение значений. Представьте, что у вас есть последовательность чисел 1, 2, 5, 10. Их общая сумма будет равна 18. Но сумма с приращением будет выглядеть иначе = ( […]

Сколько дочерних таблиц создано у родительской таблицы в PostgreSQL? select count(*) from pg_inherits where inhparent = (select oid from pg_class where relname = 't_all_dp'); Сначала нам нужно получить OID нашей родительской таблицы из представления «pg_class«. Имя нашей таблицы хранится в столбце «relname«. Затем в представлении «pg_inherits» мы отбираем все записи, […]

Как узнать ведётся ли журнал действий над таблицей в PostgreSQL или нет? При переносе больших данных из одной таблицы в другую, для ускорения процесса обычно отключают ведение журнала для новой таблицы. Для отключения логирования используется команда: ALTER TABLE a.t_clients SET UNLOGGED; После переноса данных нужно вручную включить логирование на новой […]

При переносе данных из таблицы в таблицу или при переводе таблицы в секционированную из обычной, возникает потребность в изменении последнего созданного значения для новой последовательности (SEQUENCE) если она участвовала в генерации целочисленных идентификаторов первичных ключей. alter sequence a.mytable_new_seq restart with 500228059; После этого можно будет увидеть сделанные изменения select * […]

Возможны две наиболее вероятные ситуации, когда точно нужно делать VACUUM FULL в PostgreSQL: Мы сильно меняем данные в таблицах и делаем много удалений (операций DELETE). Сотнями тысяч в сутки, и даже более. При этом место на диске не освободилось для операционной системы. Запросы начинают замедляться. Таблицы засоряются старыми ненужными страницами, […]

Именно таким поисковым запросом хочется описать суть проблемы с очисткой таблицы, для полного освобождения пустых и уже ненужных страниц данных для операционной системы. VACUUM FULL ВСЕГДА БЛОКИРУЕТ ТАБЛИЦУ ДАЖЕ ДЛЯ ЧТЕНИЯ (актуально на осень 2023)! Но проблему можно решить зайдя с другой стороны.   Какие предпосылки у такого рода запроса? […]

Перенос данных между таблицами может иметь продолжительный период. Например если таблица весит пару сотен гигабайт, а хранилище работает на HDD дисках и эти диски читают другие приложения. В результате будет разумным перенести сначала более ценные записи таблицы, а уже потом все остальные. Под ценными данными каждый будет понимать что-то своё. Но […]

Часто на сайте нужно что-то подсветить или разукрасить. А иногда, для удобства восприятия человеком, нужно чередовать тематически схожие элементы. Например когда нужно цветами чередовать записи в HTML-таблице. Но нюанс заключается именно в том, что чередование происходит не между отдельными записями таблицы, а между группами записей под какое-то определённое условие. Пример […]

Как экранировать «остаток от деления» — символ процента? В коде для PostgreSQL можно увидеть последовательно два символа процента «%%«. Мы понимаем, что это может быть обычная строка с типом text или даже шаблон регулярного выражения. Но! Всё меняется, когда нам необходимо выполнить динамическую команду PostgreSQL с использованием оператора «остаток от деления», […]

PostgreSQL даёт возможность узнать дату последнего запуска «процесса автоматической очистки таблицы» (autovacuum) в базе данных. Для этого нужно обратиться к системному представлению, которое называется «pg_stat_user_tables«. В нём нас интересует столбец «last_autovacuum«. Для идентификации нужной таблицы нам понадобятся схема (столбец «schemaname«) и имя таблицы в этой схеме (столбец «relname«). Запрос будет […]

Чтобы не натыкаться на возможные разновидности значений в столбцах, которые может использовать OVER нужно написать «OVER()«. Просто слово «OVER» и две круглые скобки после него «()«. В таком виде PostgreSQL будет проставлять нужный результат агрегирующей функции для всех записей (строк таблицы/строк запроса) с учётом какого-либо столбца. -- Пример таблицы № […]

ERROR: ОШИБКА: в элементе предложения FROM неверная ссылка на таблицу Когда пытаешься соединять различные таблицы в одну большую и информативную, то при работе с JOIN можно получить ошибку вида: ERROR: ОШИБКА: в элементе предложения FROM неверная ссылка на таблицу "rz" LINE 2: JOIN (SELECT * FROM a.t_d WHERE r = […]

Как в PostgreSQL запретить любое обновление всех существующих ячеек определённого столбца таблицы? Возможно только удаление записи с ячейкой. В PostgreSQL по умолчанию при создании таблицы предусматривается возможность изменения (UPDATE) любых данных, на которые не наложены какие-либо ограничения. Есть базовые ограничения первичного ключа или внешнего ключа. Также из популярных может быть […]

NULL-значения, которые база данных отдаёт на клиент могут не представлять никакой ценности для конечного пользователя. В большинстве случаев от них нужно избавляться. Если выбирать данные из таблиц с включением NULL-значений, тогда в остальной части приложения нужно будет делать эти проверки «на NULL» и производить подмены на что-то более внятное и […]

SQL-команда для создания дампа структуры базы, без данных pg_dump --schema-only my_super_database > /tmp/my_super_database.schema_only.dump Что тут что? pg_dump — это название утилиты(программы), которая делает дамп. —schema-only — это ключ, который говорит утилите pg_dump, что нужно выгружать только определения объектов (схемы), без данных. my_super_database — это ваше название базы данных, из которой […]

Замедление из-за предложения CONCURRENTLY Создание индекса с использованием слова «CONCURRENTLY» ровно в два раза замедляет процесс. Почему? Потому что на создание такого индекса нужно будет затратить два последовательных сканирования всей таблицы. Если у вас есть возможность заблокировать таблицу на какое-то время, то можете смело создавать индекс без слова «CONCURRENTLY«. Имейте […]

SQL-команда, при помощи которой можно получить информацию о количестве созданных временных файлов, а также о их суммарном общем размере. select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database; В результате можем получить что-то подобное:   Мы используем встроенное системное представление «pg_stat_database«. Оно содержит по одной строке со статистикой уровня базы […]

Какой первичный ключ таблицы является «следующим» для «текущего» в PostgreSQL? Очень часто приложения (сайты) с клиентской стороны требуют формирования страниц пагинаций или кнопок «следующая страница» / «предыдущая страница». Как правило они оформляются с адресами, в которые входит значение текущего идентификатора. Это число (первичный ключ) берётся из таблицы и просто транслируется […]

В PostgreSQL для создания индекса таблицы (и её столбцов) предусмотрен оператор CREATE INDEX. Создать индекс можно как по одному столбцу, так и по нескольким. Создавая индексы по нескольким столбцам важно соблюдать последовательность столбцов. Разная последовательность столбцов в индексе, может давать разный результат по обработке запросов на выборку данных через оператор […]

Для обновления индекса нужно точно знать его имя и имя схемы данных. Таблица может содержать большое количество индексов, как по полным столбцам, так и по их частичным значениям (по диапазонам значений в столбце). Обновление индекса по сути является его полным перестроением. Можно сказать, что мы производим две операции: Удаляем существующий […]

Получение последнего элемента массива без подзапросов и переворотов — вариант для вычисляемых столбцов GENERATED ALWAYS AS STORED Пример на литеральном обозначении массива: SELECT (ARRAY[11,22,33,44])[(array_length(ARRAY[11,22,33,44], 1))]; Пример на ссылочном обозначении массива: SELECT (arr)[(array_length(arr, 1))]; Вместо «arr» нужно подставить своё выражение до массива.   SQL-запрос на переворот массива и получение первого элемента […]

Как узнать общее количество записей в какой-то таблице в PostgreSQL через агрегатную функцию count()? SELECT count(*) FROM a.t_d; Результат работы выражения: Мы используем команду SELECT, для того, чтобы получить какой-то результат. После команды мы указываем агрегатную функцию count() внутрь которой передаём символ звёздочки. Смысл функции — подсчитывать общее количество записей. […]

Как сделать SQL-дамп данных одного столбца таблицы PostgreSQL на ПК в отдельный файл? У нас есть таблица с несколькими столбцами и несколькими записями. Мы хотим сохранить на компьютер данные только из одного столбца этой таблицы, чтобы у нас получился файл с расширением «.sql«. Этот файл нам нужен для хранения наиболее […]

SQL-команда примерно такая: -- в байтах SELECT sum(pg_column_size(id)) FROM a.t_d; -- или в человеко-понятном формате SELECT pg_size_pretty(sum(pg_column_size(id))) FROM a.t_d; Мы используем встроенную функцию pg_column_size(), которая показывает нам размер занимаемой ячейки таблицы. Мы обходим каждую запись таблицы и вычисляем размеры ячеек.   Применив агрегатную функцию sum(), мы можем просуммировать все значения размеров […]

Как получить предыдущую запись таблицы по предполагаемому первичному ключу? Суть проблемы заключается в том, что при использовании генераторов последовательностей SEQUENCE, можно получать «дырки» в первичных ключах. Это происходит потому, что каждая большая база данных имеет свойство обновляться. Может возникать необходимость в удалении записей в таблицах. Стало быть, если запись удаляется, то […]

Исходная таблица со столбцами Прежде чем проверять существования имён столбцов таблицы, давайте создадим её. -- Тестовая таблица -- DROP TABLE test.t_2022_12_19; CREATE TABLE IF NOT EXISTS test.t_2022_12_19 ( col1 text, col22 text, col333 text, col4444 text ); В веб-интерфейсе pgAdmin4 она выглядит так: Данных в таблице нет. Но они нас […]