Как подсчитать количество уникальных значений столбца таблицы в PostgreSQL? Для получения результата нам понадобится таблица с данными. Вводные данные:   -- Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_11_18 (     id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 […]

Вводные данные:   -- Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_11_18 (     id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),     randomtext text,     CONSTRAINT t_2022_11_18_id_pkey PRIMARY KEY (id) )   -- Вставляем тестовые […]

Иногда бывает полезным получать не всё временное значение, а только какую-то его часть. Например, мы хотим получить текущий год, без даты, без времени, без миллисекунд и так далее. Как это сделать?   -- Стандарт PostgreSQL SELECT extract(YEAR FROM NOW()); -- Стандарт SQL SELECT CURRENT_TIMESTAMP;   Данное выражение вернёт нам числовое […]

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

У нас есть простая оригинальная таблица. SQL команды для её создания такие:   -- Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_11_16 (     id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),     username text,     […]

У нас есть строка в PostgreSQL: SELECT 'https://21657734.xxx734.com'; Нам нужно разделить эту строку на основе двух слешей. То есть мы хотим получить доменное имя с корневой зоной без схемы. Как это сделать? Для решения задачи нужно использовать функцию regexp_split_to_array(), которая умеет принимать регулярные выражения вторым параметром. SELECT regexp_split_to_array('https://21657734.xxx734.com', '\/\/'); Результат: […]

Делая свой первый ДАМП базы данных в PostgreSQL на Debian-сервере, можно столкнуться с проблемой "Отказано в доступе (Permission denied)". В терминале Debian мы вводим команды от пользователя postgres, который автоматически появляется в системе при установке PostgreSQL в Debian. Если попытаться сделать ДАМП от имени другого пользователя (например от "root"), то […]

Перед решением задачи по поиску самого повторяющегося числа из массива JavaScript нужно для себя прояснить несколько условий, с которыми можно столкнуться.   Условие № 0 - Фильтрация массива на числа Если в массиве есть объекты отличные от Number, то нам они не нужны их можно спокойно выбросить.   Условие № […]

PostgreSQL - это какая-то "каша" из разных концепций языков программирования, слепленных в одну кучу. В одном месте индексы массивов начинаются с единицы, и тут же в другом месте индексы массивов начинаются уже с нуля. Это как пример убогости. Так вот. Столкнулся с проблемой обратной конвертации ячейки из JSONB обратно в […]

Решение № 1 - Через сортировку всей таблицы и функцию RANDOM() в PostgreSQL Это самый простой в понимании вариант, но самый медленный. Команда для получения одной случайной записи из таблицы: SELECT * FROM a.t_d ORDER BY RANDOM() LIMIT 1; Ключевое выражение здесь RANDOM(). Оно генерирует случайное число в диапазоне 0.0 <= […]

Для добавления нового столбца в существующую таблицу в базе данных PostgreSQL нужно воспользоваться оператором изменения таблицы, который называется ALTER TABLE. Исходная таблица с данными выглядит так: CREATE TABLE test.t15102022 ( id bigint NOT NULL DEFAULT 0, name text NOT NULL ) INSERT INTO test.t15102022 VALUES (1, 'Вася'), (2, 'Петя'); SELECT […]

Мы работаем в PostgreSQL и у нас есть задача поискать нужное слово в массиве из строк. Как это сделать и в чём может быть проблема?   Исходный массив со словами на русском языке в PostgreSQL Массив будет таким: SELECT ARRAY['Мотоцикл', 'Самолёт', 'Автомобиль']; Скриншот из pgAdmin4: Если мы попытаемся в лоб […]

Как получить данные из одной записи таблицы в виде JSON объекта, а не таблицы? У нас есть таблица с данными. Выглядит она примерно так: Мы хотим получить одну запись этой таблицы в виде JSON-объекта, а не строки таблицы с ячейками. Как это сделать?   Функция row_to_json() В PostgreSQL для создания […]

Как узнать полное количество дней между одной датой и другой датой в PostgreSQL? У нас есть две даты: SELECT '2022-07-11 09:36:49.388216'::timestamp without time zone; SELECT '2022-09-29 16:55:45.104096'::timestamp without time zone; Нам нужно узнать разницу в днях между самой новой датой и самой старой датой. Как это сделать?   Вычитание дат […]

У нас есть символьный код 1067. Мы не знаем что это за символ, но хотим делать по нему проверку строк.   Как конвертировать один символьный код в символьную строку при помощи PostgreSQL? В решении задачи конвертации символьного кода нам поможет встроенная в PostgreSQL функция chr(). Она принимает целое число, а […]

Приведение символов в нижний регистр очень часто связано с задачами сопоставления строк на равенство. Как правило, это поиск информации без учёта регистра. Как решить подобную задачу в 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 через дополнительный массив из одного элемента строки и оператора @> Для нашей задачи это […]