Приведение символов в нижний регистр очень часто связано с задачами сопоставления строк на равенство. Как правило, это поиск информации без учёта регистра. Как решить подобную задачу в PostgreSQL? Предположим у нас есть предложение на русском языке: SELECT 'Привет Мир!'; В предложении всего два слова и они начинаются с заглавной буквы. […]

В PostgreSQL есть специальные встроенные строковые функции, при помощи которых можно работать со строковыми типами данных, например с типом text. Нужна для нашей задачи функция называется substr(). Она может принимать два или три параметра. Первый параметр - это строка, из которой мы будем получать первые два символа. Второй параметр - […]

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

Пример таблицы   Для выборки В условии WHERE мы должны указать нужный столбец, а затем прописать выражение IS NULL. Дословно оно обозначает - "Является Наллом". SELECT * FROM a.t_d WHERE datecheck IS NULL LIMIT 100; Мы добавили выражение LIMIT со значением 100, потому что в таблице может быть очень много […]

Есть таблица с неполным набором данных: -- Создаём таблицу CREATE TABLE test1.rrr ( xname text, xdate timestamp without time zone ); -- Наполняем неполными данными INSERT INTO test1.rrr VALUES ('Иван'); INSERT INTO test1.rrr VALUES ('Максим'); INSERT INTO test1.rrr VALUES ('Николай'); INSERT INTO test1.rrr VALUES ('Сергей'); INSERT INTO test1.rrr VALUES ('Дмитрий'); […]

Предполагается, что у нас есть какая-то таблица. В ней есть столбец с ДАННЫМИ, а также столбец с ДАТАМИ добавления (или обновления) этих данных. Пример таблицы для теста: -- DROP TABLE test1.olddate; -- Создаём таблицу CREATE TABLE test1.olddate ( xname text, xdate timestamp without time zone ); -- Наполняем данными INSERT […]

Есть таблица: -- Создаём таблицу CREATE TABLE test1.np ( name text, product text ); -- Наполняем данными INSERT INTO test1.np VALUES ('Иван', 'Молоко'); INSERT INTO test1.np VALUES ('Иван', 'Кефир'); INSERT INTO test1.np VALUES ('Николай', 'Молоко'); INSERT INTO test1.np VALUES ('Сергей', 'Кефир'); INSERT INTO test1.np VALUES ('Сергей', 'Кефир'); INSERT INTO test1.np […]

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

Столкнулся с проблемой удаления одного индекса таблицы в PostgreSQL. Индекс точно есть, его можно проверить: SELECT * FROM pg_indexes; Стандартная команда удаления не срабатывала и я не мог понять почему: DROP INDEX mytabin; Вызов этой команды выдавал ошибку: ERROR: ОШИБКА: индекс "mytabin" не существует SQL state: 42704   Пришлось повнимательнее […]

Представление pg_indexes даёт доступ к полезной информации обо всех индексах в базе данных. На 2022 год представление pg_indexes имеет 5 столбцов: schemaname name (ссылается на pg_namespace.nspname) Имя схемы, содержащей таблицу и индекс tablename name (ссылается на pg_class.relname) Имя таблицы, для которой создан индекс indexname name (ссылается на pg_class.relname) Имя индекса […]

Предположим, что у нас есть массив в PostgreSQL: SELECT ARRAY['один', 'два', 'три']; Результат вызова: Мы хотим проверить существование строки 'четыре' в этом массиве. Как это сделать?   Решение задачи по проверки существования строки в массиве PostgreSQL через дополнительный массив из одного элемента строки и оператора @> Для нашей задачи это […]

Удаление нужного количества записей таблицы в PostgreSQL сводится к двум задачам: Написание ПОДЗАПРОСА с лимитом, который возвращает столбец с идентификаторами Написание удаления с выражением IN по этим идентификаторам   Выглядит это примерно так: -- DROP TABLE IF EXISTS test.rrr; CREATE TABLE IF NOT EXISTS test.rrr ( id serial, mfg bigint […]

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

Пример SQL-команды, которая выведет длину одномерного массива: SELECT array_length(ARRAY[8,6,4], 1); Функция array_length() принимает два параметра: Сам массив, по которому нужно вычислить длину Размерность массива Если массив состоит только из идентификаторов целых чисел, то размерность выставляем на 1.   Другие примеры в зависимости от глубины массива и размерностей вложенных (глубинных) массивов […]

В чём проблема получения таблицы с данными по запросу SELECT? У нас есть таблица с доменными именами. Мы знаем как отобрать нужные нам ЗАПИСИ из этой таблицы в виде ТАБЛИЦЫ.   Пример SQL-запроса в PostgreSQL: SELECT * FROM a.t_dname WHERE dname LIKE '%.tumblr' LIMIT 5; Результат работы запроса: Мы также […]

Пример SQL-запроса: Такой вариант сильно зависит от количества повторений в idd SELECT DISTINCT ON (idd) * FROM (SELECT * FROM a.t_all_dp WHERE pagestatus = 0 ORDER BY RANDOM() LIMIT 100) AS ee LIMIT 10; Этот вариант более правильный (более случайный), так как не зависит от повторений в idd SELECT * […]

Может оказаться такая ситуация, что после установки операционной системы Debian, вместе с ней установился пакет с СУБД PostgreSQL версии 11. На август 2022 уже запланирован выпуск 15 версии PostgreSQL. СУБД динамично развивается и сообщество пополняется всё большим количеством администраторов этой СУБД. Из-за этих особенностей Debian, для получения полного свежайшего функционала […]

С этим вопросом впервые сталкиваешься, когда пытаешь обновить версию кластера PostgreSQL. Например с 11 версии до 14. Запуская команду "/usr/lib/postgresql/14/bin/pg_upgrade" можно столкнуться с проблемой: Видимо, запущен процесс postmaster, обслуживающий старый кластер. Остановите его и попробуйте ещё раз. Ошибка, выполняется выход Скриншот: Всё очевидно. Даже на русском языке подаётся ответ. Нужно как-то […]

По умолчанию лог обновления версии PostgreSQL будет расположен в операционной системе Debian в директории: /var/lib/postgresql   Информационные ссылки Официальный сайт WEB-оболочки pgAdmin - https://www.pgadmin.org Официальный сайт СУБД PostgreSQL - https://www.postgresql.org

-- Уникальность по двум столбцам   CREATE TABLE test.t_un (     x bigint,     y bigint,     CONSTRAINT t_un_xy_unique UNIQUE (x, y) )   -- Просмотр таблицы SELECT * FROM test.t_un;   -- Вставка нормальных данных INSERT INTO test.t_un VALUES (1,1), (1,2), (2,1), (2,2);   -- Вставка […]

То что тебе нужно называется SIMILAR TO. Оператор SIMILAR TO точно также, как и LIKE, умеет работать с процентами % и нижними подчёркиваниями _, а для того чтобы выбрать одно из условий, нужно обернуть варианты в круглые скобки ( ) и между вариантами расставить вертикальные палочки |. Пример работы оператора […]

Как коротко записать удаление записей таблицы с определёнными идентификаторами, чтобы не расписывать OR? Решение есть. И этот оператор называется IN. Давайте для примера создадим тестовую таблицу: -- Удаление записей из таблицы через список значений условия -- без повторяющихся AND   -- Создаём тестовую таблицу CREATE TABLE test.rrr (     […]

Простая ситуация - таблица точно существует Допустим мы 100% уверены, что таблица к которой мы обращаемся существует. Нам не нужно переживать, что при обращении к ней вывалится ошибка. Проще всего проверить существующую таблицу на пустоту - это использовать функцию count() для подсчёта количества записей в таблице. Если записи есть в […]

В чём проблема? Столкнулся с такой проблемой, что PostgreSQL не умеет приводить тип данных "большого целого числа" к "логическому типу". SELECT CAST (9223372036854775807 AS boolean); Скриншот из pgAdmin4: ОШИБКА - привести тип bigint к boolean нельзя.   Это весьма неожиданно, особенно когда приходишь в SQL с какого-нибудь языка программирования, где […]

Когда ты только начинаешь изучать PostgreSQL, не всегда понятен смысл тех или иных операторов, определений и выражений. Одним из таких не очевидных понятий является "ВНЕШНИЙ КЛЮЧ" или "FOREIGN KEY". Я приведу несколько примеров зачем он нужен.   Зачем № 2 - Каскадное удаление данных Если ваши таблицы не будут связаны, […]

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

У любой базы данных в PostgreSQL есть такая схема данных, которая называется "information_schema". В тексте мы будем называть её "Информационная схема". "Информационная схема" состоит из набора представлений, содержащих информацию об объектах, определённых в текущей базе данных. "Информационная схема" не относится к каталогам самого PostgreSQL. "Информационная схема" описана в стандарте SQL, […]

Проще всего изменить данные в конфигурационном файле "postgresql.conf", который лежит в папке "/etc/postgresql" в операционной системе Debian. Для этого копируем файл с сервера, открываем его в редакторе кода и находим соответствующую запись конфигурации:   Всё что нам нужно сделать это удалить символ решётки напротив параметра "max_locks_per_transaction" и вместо значения 64 […]

Команда в PostgreSQL: SHOW max_locks_per_transaction; Выведет табличный результат с одной ячейкой, в которой будет указано текущее значение блокировок количества объектов на транзакцию.   По факту этот параметр участвует в формуле вычисления общего количества одновременных блокировок для "общей таблицы блокировок": max_locks_per_transaction * (max_connections + max_prepared_transactions) В общем итог блокировок объектов зависит […]

В этой публикации я хочу разобрать вопрос "каскадного удаления данных" из таблиц на простых примерах. Тема важная и её понимание избавляет от написания бесполезных проверок или функций.   Ситуация № 1 - Удаляя оригинал данных в одной таблице, мы хотим чтобы удалились все зависимые от этого оригинала данные в других […]

У нас есть таблица. В таблице есть столбец со строковым типом данных. Мы хотим отобрать несколько записей из этой таблицы, в которых присутствует часть строки. В нашем случае мы хотим получить идентификаторы записей, где в столбце "dname" встречается ".tumblr" на конце строки. Как это сделать?   Решение Для решения задачи […]

Для проверки существования записи в таблице PostgreSQL нужно использовать связку команды SELECT и команды EXISTS. SELECT EXISTS (SELECT * FROM a.t_dname WHERE dname = 'efim360'); В этом примере мы хотим получить ЛОГИЧЕСКОЕ ЗНАЧЕНИЕ -  TRUE или FALSE. Если в таблице t_dname существует запись со значением 'efim360' в столбце dname, то […]

Команда для получения одной случайной записи из таблицы: SELECT * FROM a.t_d ORDER BY RANDOM() LIMIT 1; Ключевое выражение здесь RANDOM(). Оно генерирует случайное число в диапазоне 0.0 <= x < 1.0. Это значит, что НОЛЬ может быть, а ОДИН никогда не будет. В JavaScript это точно также, например.   […]