PostgreSQL

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-оболочки […]

У нас есть дата с типом «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 15:04:06.180197+03'::date + INTERVAL '15D' )::date, '1D')::date; В […]

Для удаления столбца из существующей таблицы в базе данных 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, […]

Функции округления: ceil( ) — округление до наибольшего целого floor( ) — округление до наименьшего целого round( ) — округление до ближайшего целого trunc() — округление до целого в сторону нуля   Округление числа до наибольшего целого в PostgreSQL SELECT ceil(4.75); 5 SELECT ceil(-4.75); -4 SELECT ceil(2.1); 3 SELECT ceil(-2.1); […]

Для добавления нового столбца в существующую таблицу в базе данных 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 есть много записей. Каждая запись использует столбец даты появления самой себя в этой таблице. Столбец с датами создания называется «datecreate«. Проблема заключается в том, что даты имеют тип «timestamp with time zone«. То есть помимо года, месяца и дня, формат даты имеет часы, минуты, […]

PostgreSQL предлагает нам основные 6 типов для работы с датами и временами: timestamp without time zone timestamp with time zone date time without time zone time with time zone interval Так вот проблема может заключаться в том, что для вычисления интервала мы можем передавать не те типы (либо разные типы). […]

Имеются ввиду записи таблицы, которые в одном из столбцов имеют дату, подходящую под понятие «СЕГОДНЯ«. Это все даты которые начались с времени 00:00 до текущего времени или до конца текущего дня, то есть до 24:00. Перед решением задачи нужно знать в каком формате «дата/время» записываются данные в ячейки. Какой тип […]

Выражение в PostgreSQL будет выглядеть примерно так: SELECT * FROM scheme.my_table WHERE date_trunc('day', datecreate) = date_trunc('day', CURRENT_TIMESTAMP - interval '1D'); или с лимитом, если записей может быть много SELECT * FROM scheme.my_table WHERE date_trunc('day', datecreate) = date_trunc('day', CURRENT_TIMESTAMP - interval '1D') LIMIT 1000; В результате мы отберём все возможные записи […]

Предположим у нас есть простая таблица с одним столбцом: -- Создаём новую тестовую таблицу CREATE TABLE test1.names ( name text ); -- Заполняем таблицу данными INSERT INTO test1.names VALUES ('Иван'); INSERT INTO test1.names VALUES ('Иван'); INSERT INTO test1.names VALUES ('Николай'); INSERT INTO test1.names VALUES ('Сергей'); INSERT INTO test1.names VALUES ('Сергей'); […]

WITH arr AS ( SELECT ARRAY[11, 22, 33, 44, 55] AS qwe ) SELECT ARRAY[  qwe[array_length(qwe, 1)-1],    qwe[array_length(qwe, 1)]   ] FROM arr; В подзапросе мы получаем оригинальный массив. Это тот из которого нужно достать последние два элемента. Затем возвращаем новый массив где: Первый элемент нового массива это qwe[array_length(qwe, 1)-1] […]

Переворот массива подзапросами в PostgreSQL SELECT ARRAY(SELECT array_item FROM unnest(ARRAY[1, 2, 3, 4, 5]) WITH ORDINALITY AS t(array_item, position) ORDER BY position DESC); или SELECT ARRAY(SELECT array_item FROM unnest( ТВОЙ_МАССИВ ) WITH ORDINALITY AS t(array_item, position) ORDER BY position DESC); Как отзеркалить массив? Чтобы не писать отдельную функцию, достаточно добавить […]

Представим, что у нас есть строка доменного имени и мы хотим получить из неё все записи, которые находятся между строчками: SELECT * FROM unnest(string_to_array('http://www.worcestergold.org', '.')); Но мы хотим получить не один столбец в таблице, а два. Второй должен содержать последовательную нумерацию частей после разделения строки на элементы массива.   Для […]

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