PostgreSQL

Существует несколько способов присваивания значений в свойствах 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 она выглядит так: Данных в таблице нет. Но они нас […]

Исходная таблица со столбцами Прежде чем получать имена столбцов таблицы, давайте создадим её. -- Тестовая таблица -- 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 она выглядит так: Данных в таблице нет. Но они нас не […]

Исходная таблица со столбцами Прежде чем получать имена столбцов таблицы, давайте создадим её. -- Тестовая таблица -- 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 она выглядит так: Данных в таблице нет. Но они нас не […]

Есть таблица: -- Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_12_15 ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), username text COLLATE pg_catalog."default", randomcode text DEFAULT substr(md5(random()::text), 1, 5), CONSTRAINT t_2022_12_15_id_pkey PRIMARY KEY (id) ); -- Вставляем […]

Приведение текущей временной метки к типу date SELECT CURRENT_TIMESTAMP::DATE; Скриншот результата приведения:   Приведение любой временной метки, объявленной литерально, к типу date через двойное двоеточие SELECT '2022-12-05 13:20:05.464379+03'::DATE; Скриншот результата приведения:   Приведение любой временной метки, объявленной литерально, к типу date через CAST() SELECT CAST('2022-12-05 13:20:05.464379+03' AS DATE); Скриншот результата […]

Дата может быть оформлена различными типами. Но для извлечения «ГОДА» нужно использовать функцию extract(). SELECT EXTRACT(YEAR FROM '2020-12-17 10:30:00.12742+03'::timestamp); или SELECT EXTRACT(YEAR FROM '2020-12-17'::timestamp); Получим 2020. Смысл в том, что любую дату можно приводить к типу timestamp, а уже потом извлекать год.   Информационные ссылки Официальный сайт WEB-оболочки pgAdmin — […]

Как обойти таблицу в PostgreSQL при помощи цикла FOR? У нас имеется таблица: -- Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_12_15 ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), username text COLLATE pg_catalog."default", randomcode text DEFAULT substr(md5(random()::text), […]

Как обойти массив в PostgreSQL при помощи цикла FOREACH? Через команду DO мы имитируем работу функции, в которой описан простейший цикл по массиву: -- Цикл по массиву DO $$ DECLARE -- Исходный массив arr text[] := '{Лена,Маша,Ян,Петя,Вася}'; -- Переменная для хранения одного элемента массива при обходе циклом x text; BEGIN […]

Команда получения одной случайной буквы русского алфавита из 66 возможных символов в PostgreSQL SELECT (regexp_split_to_array('абвгдеёжзийклмнопрстуфхцчшщъыьэюяАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ', ''))[ceil(random()*66)];   Команда получения одной случайной буквы русского алфавита из 33 возможных символов в PostgreSQL SELECT (regexp_split_to_array('абвгдеёжзийклмнопрстуфхцчшщъыьэюя', ''))[ceil(random()*33)];   Информационные ссылки Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org Официальный сайт СУБД PostgreSQL — https://www.postgresql.org Команды SQL […]

Мы хотим получать случайное целое число в диапазоне от нуля до шестидесяти шести в PostgreSQL. Как это сделать? SELECT ceil(random()*66); Функция random() выдаёт случайные значения от 0 (включительно) до 1 (не включая 1). Это значит, что если мы просто умножим это значение на 66, то никогда не получим его ровно. […]

У нас есть строка, в которой последовательно идут 66 букв русского алфавита. 33 символа — строчные. 33 символа — заглавные. Мы хотим разделить эту строку по каждой одной букве и положить эти буквы в массив. Как это сделать в PostgreSQL?   Решение Для решения задачи по разделению строки мы воспользуемся […]

RETURNS или RETURNS TABLE без определённого количества полей и типов данных В чём проблема? Когда мы пишем функцию в PostgreSQL, тогда выражения RETURNS или RETURNS TABLE ждут от нас явного количества передаваемых параметров и их типов. Иными словами. Если мы работаем с таблицей, то функция сломается, когда у таблицы появятся […]

У нас есть два набора идентификаторов. Это выглядит как два столбца таблицы. SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9]); SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13]); Я называю словом «набор», потому что каждый элемент уникален. Первый столбец с идентификаторами — это тот из которого мы хотим вычесть второй столбец. Мы из первого вычитаем второй. Именно так. Потому что от перемены […]

Какие идентификаторы встречаются в двух столбцах? У нас есть два набора идентификаторов. Это выглядит как два столбца таблицы. SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9]); SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13]); Я столбцы называю словом «набор», потому что каждый элемент столбца уникален. Мы хотим найти все возможные пересечения между первым и вторым столбцами. Мы хотим знать какие идентификаторы есть, […]

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

Если ошибка вплывает по причине уникальности, то можно просто ничего не делать с подобной вставкой новой записи и переходить к следующей. Для этого поможет предложение ON CONFLICT DO NOTHING. Ниже пример SQL-запроса на вставку данных: INSERT INTO scheme.table(t_column) SELECT unnest(ARRAY[1,2,3,4,5]) ON CONFLICT DO NOTHING;   Информационные ссылки Официальный сайт WEB-оболочки […]

Для удаления столбца из существующей таблицы в базе данных PostgreSQL нужно воспользоваться оператором изменения таблицы, который называется ALTER TABLE. Исходная таблица с данными выглядит так: -- Создали таблицу CREATE TABLE IF NOT EXISTS test.t_11_12_2022 ( id bigint, name text ); -- Ставили данные INSERT INTO test.t_11_12_2022 (id, name) VALUES (1, […]