PostgreSQL

В 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 это точно также, например.   […]

При оптимизации разработки в PostgreSQL иногда очень удобно складывать похожие по смыслу таблицы в одну схему данных. В некоторых случаях они могут генерироваться автоматически для промежуточного хранения результатов. При таком подходе всегда понятно, что там лежит. Остаётся только одно - а сколько "этого" там лежит? Много или мало? Как посчитать? […]

Иногда нужно проверять существование таблицы в базе данных PostgreSQL. В случае её наличия мы делаем одни действия, а в случае её отсутствия - другие. Приложение может упасть в ОШИБКУ, если мы будем пытаться обращаться к данным таблицы, которой нет. Мы же не хотим, чтобы всё у нас отвалилось. Поэтому нам […]

В объектно-ориентированных языках типа JavaScript существуют встроенные классы со своими конструкторами. Когда из конструктора класса рождается новый объект, то он наследует все свойства экземпляра класса. Так в JS все массивы имеют унаследованный метод push(), который тупо делает вставку нового значения в конец массива, на котором был вызван. В случае с […]

В нормальных языках программирования параметры функций могут вычисляться через вызовы других функций прямо в блоке объявления параметров. Это очень удобно. Но в PostgreSQL и прочих SQL намудрили, накрутили и запутали большинство людей.   Задача Нам нужно склеивать полные URL-адреса из составных частей, которые разложены по ячейкам таблиц. Вот объекты для […]

Изучая СУБД PostgreSQL можно часто слышать слово "Представление" (View) рядом со словом "Таблица" (Table). Что это такое простыми словами? Представление - это способ задать ИМЯ для запроса SELECT на выборку данных и по этому ИМЕНИ быстро получать нужную "собранную" таблицу. То есть можно создать сложный запрос в базу данных и дать […]

Как сделать подобие console.log() как в JavaScript? В функциях PostgreSQL иногда полезно выводить промежуточные результаты в консоль, чтобы понимать как работает алгоритм функции. Нужный нам оператор (команда) называется: RAISE NOTICE Команда RAISE предназначена для вывода сообщений и вызова ошибок. Она может быть написана в коротком виде. Но в нашей задаче […]

Сколько места на диске занимает таблица с данными в PostgreSQL, учитывая всё? Есть таблица cars в схеме test1 в базе данных sdb:   Решение Чтобы узнать ПОЛНЫЙ размер одной таблицы PostgreSQL на диске нужно воспользоваться встроенной в СУБД функцией pg_total_relation_size(regclass) SELECT pg_total_relation_size('sdb.test1.cars'); Оператор SELECT здесь нужен для получения табличного ответа. Внутрь […]

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

Чтобы изолировать одинарную кавычку внутри всего выражения EXECUTE и не использовать её в качестве синтаксиса самого EXECUTE, нужно дописать к исключаемой одинарной кавычке ещё одну такую же одинарную кавычку. Синтаксический анализатор выражения EXECUTE сможет правильно понять вас.   Если в обычном SQL-запросе есть выражение: COMMENT ON COLUMN b.t_d_55.idp IS 'Идентификатор […]

Как автоматически проставлять текущую ДАТУ и ВРЕМЯ при добавлении записи в таблицу? Давайте начнём с того какие вообще типы данных для ДАТЫ и ВРЕМЕНИ существуют:   PostgreSQL предлагает нам основные 6 вариантов. timestamp without time zone timestamp with time zone date time without time zone time with time zone interval […]

У нас имеется связующая таблица из идентификаторов первичных ключей по другим трём таблицам: SELECT * FROM a.t_d; Скриншот таблицы:   Мы делаем соединение JOIN для визуального представления данных: Мы извлекаем строковые значения из трёх таблиц и подставляем их в результирующую таблицу по идентификаторной привязке. В результате мы видим что-то похожее […]

У нас есть таблица в PostgreSQL с JSON-объектами в одном столбце: Нам нужно получить значение "www.efim360" из ключа "d". Как это сделать? Как дотянуться до этого значения?   Давайте сначала отберём одну запись, в которой есть искомое нами значение и получим значение по одному столбцу: SELECT jsonsdr FROM a.t_sdr WHERE […]

Когда мы пишем стандартный код вставки записи в таблицу в PostgreSQL, то мы можем указать для команды INSERT атрибут RETURNING. Справа от RETURNING мы должны указать имя столбца, по которому нужно возвращать значение. Одна вставка - один возврат. Пример: insert into test1.cars (brand, model, year, mileage, price, color, typeengine) values […]

Для создания переменной внутри тела функции нужно использовать конструкцию DECLARE Выглядеть это будет так: -- Функция с переменной через оператор DECLARE и := CREATE FUNCTION test1.aa23() RETURNS bigint LANGUAGE 'plpgsql' AS $BODY$     DECLARE ss bigint := 10;     BEGIN -- Обязательно BEGIN         RETURN […]

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

У нас есть строка: 'https://efim360.ru' Мы хотим проверять валидность данной строки к URL-адресу. Наш валидатор примитивный. Мы будем просто проверять начинается ли наша строка на "http://" или "https://". Как это сделать?   Решение Пусть у нас будет таблица tempurl в схеме test1: CREATE TABLE IF NOT EXISTS test1.tempurl ( id […]