Открыть список
Как стать автором
Обновить

Комментарии 58

Про similarity не знал. Могло бы сэкономить кучу времени в некоторых задачах, например, со строковыми адресами.
Как раз решаю сейчас задачи, с которыми similarity справляется на ура. Спасибо, автор! Пошел удалять лишний код.
Думаю, с приведением типов любой разработчик, использующий postgres рано или поздно столкнется, а вот про функции, подобные similarity жду следующий пост.
Рад, что статья популярна не только из-за картинки (хотя, над ней я тоже попотел).
similarity и для меня была 'палочкой выручалочкой', грустно, когда и не предполагаешь, что СУБД может такое 'вытворять'.
Я бы с удовольствием выпустил «Часть 2», знать бы в сторону каких функций и фишек смотреть…
Курсоры — www.postgresql.org/docs/9.6/static/plpgsql-cursors.html
Процедурный язык PL/pgSQL — www.postgresql.org/docs/current/static/plpgsql.html
Я считаю, что на это стоит обратить внимание. Я бы сказал, что без использования этих вещей postgresql и не postgresql вовсе.
Самое забавное, что раньше (на postgres 8.*) я использовал самописную функцию similarity в теле которого была функция Левенштейна (на 9.2 она затерлась, и я думал, что разница в результатах появилась из-за новой версии СУБД).
Я вырезал участок своего негодования из статьи, т.к. он даже не совпадал с документацией postgres 8, а перепроверив тело функции, я понял свою ошибку.
В спойлере можно почерпнуть разницу между similarity и levenshtein

Вырезанный участок, имеются ошибочные домыслы
Стоит отметить, что результат зависит от версии БД. Желание накатать подобную обучающую статью у меня возникло еще в январе (активно начал писать, потом благополучно забил, и лишь сейчас (октябрь) решил продолжить). Еще тогда я подготовил пример на Postgre 8.* (теперь то мы знаем, что это был levenshtein), получив нижеприведенный результат, который пришлось переделать.


Изменилось направление сортировки (теперь 1 это полное совпадение) (функция лишь была так описана), перестали учитываться знаки пунктуации и регистр, ушло ограничение в 255 символов. Одно мне не понравилось, что на моей текущей версии Postgres 9.2 выдало 2 строки с похожестью 1, ибо одна из них не является полностью идентичной (может это к лучшему). Возможно, есть дополнительные настройки, но я не углублялся в это т.к. не было необходимости.

P.S. Только жаль, что SOUNDEX не очень подходит для русского языка.
О, как! Есть готовое. Сам то всегда реализовывал такую задачу методом Q-грамм (Би-грамм если быть точным). Надеюсь, производительность реализации позволит в лоб сопоставить две таблицы хоты бы 10к в каждой.
«в лоб» будет медленно, т.е. надо для каждой строки из первой таблицы найти по всем записям similarity второй таблицы, сортирнуть по ней, и взять с наивысшим соответствием. Вот именно так я вчера и проверял, у меня уходило секунд ~6 на запись (limit 10 всего запроса работал минуту).

Правда у меня объём был 230к, зато реальных данных. И связка очень красиво так получилась! Адреса разной структуры — в одном опущена область, в другом индекс, первый структурированный с разделителем, второй ручной ввод.

Естественно некий процент ошибок будет, но это лучше, чем всё лопатить руками.
Ну! 6 сек, на 230к строк … да небось в Unicode, со средней длиной ~50 символов – это круто!
Может и не 6, а 10… что-то засомневался, доберусь до БД проверю.
Сама функция работает мгновенно, т.е. select field и select similarity(field, 'static text') работает одинаково практически.
Проверяет соответствие регулярному выражению с учётом регистра

LIKE использует не регулярные выражения, а свой синтаксис шаблонов. Кто это знает, понял, что имелось в виду, а кто не знает, мог подумать, что туда надо писать regexp.
Действительно, вышло не так как я хотел. Поправил
Про временные таблицы есть ограничения, связанные с хранением версий строк: очень интенсивное их использование приводит к распуханию системных таблиц и снижению производительности, аналогично постоянным update обычных таблиц.
Как альтернативу временным таблицам могу предложить переменные типа jsonb или array композитных типов, правда индексы тут уже не применить, в отличие от временных таблиц. К слову, jsonb делает работу с данными в plpgsql гораздо удобнее, рекомендую взять на вооружение.
Спасибо, не знал о таком! Моя цель была просто продемонстрировать возможности, и подбить профессионалов на написание подобных статей.
для типа jsonb возможно применить индексы типа GIN

Дока
Индексы в jsonb? Легко.
CREATE INDEX station_synonyms_idx
  ON station
  USING btree
  ((additional_data #>> '{synonyms}'::text[]) COLLATE pg_catalog."default");


Это индекс по текстовому массиву. Думаю, нет смысла приводить примеры для простых строковых или числовых индексов.
По колонке в таблице — легко, а тут вместо temp table предлагаю переменную типа jsonb для промежуточного хранения. Если внутри json-а просто dict, то вытаскивание значения по ключу происходит быстро.

Но если в переменной большой массив dict-ов, то как из него вытащить пару значений по каким то условиям, не перебрав его целиком? Индексы на переменные типа jsonb не повешать.
postgres_fdw, если память не изменяет, в 9.3 появился? А в более ранних версиях только dblink.
Если не ошибаюсь, в dblink в плане транзакций довольно странно: транзакциями наверное можно управлять явно на уровне выражений, но документация этот момент не описывает. В fdw управление транзакциями происходит автоматически.
Для удалённого вызова функций удобнее использовать pl/proxy или plexor вместо fdw. В первом, насколько знаю, также нет автоматического управления транзакциями, во втором оно автоматическое.

Еще надо не забывать, что функции GREATEST и LEAST, в отличии от реализации в Oracle и DB2 LUW, игнорируют NULL.


SELECT GREATEST(1, 2, 3, NULL, 4, 5) -- 5

SELECT GREATEST(1, 2, 3, NULL, 4, 5) FROM DUAL; -- NULL

SELECT GREATEST(1, 2, 3, NULL, 4, 5) FROM SYSIBM.DUAL; -- NULL
Вот, кстати, прямо сейчас наткнулся на проблему с GREATEST и NULL. По задаче надо выбрать все положительные числа, вместо отрицательного — ноль. Но с сохранением NULL, а она не сохраняет его.
На скорую руку можно обойтись таким костылём:
SELECT GREATEST(field, 0) * (field::integer::boolean::integer)

Пояснение:
Тип поля — numeric, сначала преобразуется в integer, затем в boolean затем опять в integer. В итоге при любом значении поля, отличном от нуля, получим единицу на выходе. Или NULL, если field IS NULL.

Крутое решение, хотя я бы все же сделал через старый добрый CASE .. THEN .. ELSE .. END, тогда результат был такой же, однако тому кто потому будет доробатовать это дело не пришлось бы думать, а что вот здесь вот происходит


WITH TEST_DATA as (
  SELECT unnest(ARRAY[1,NULL,42,-3,0,2,-15,NULL,55])::int field
)
SELECT
  field
  , GREATEST(field, 0) * (field::integer::boolean::integer) option_1
  , CASE WHEN field < 0 THEN 0 ELSE field END option_2
FROM TEST_DATA
Естественно, если это код будет храниться. Если это разовый наколеночный запрос иногда костыли выручают. Ну и пример ниже придётся довольно сложно раскладывать
select field1 * (a>b)::integer + field2 * (c>d)::integer + field3 * (e = f)::integer

Собственно, вопрос в том, нет ли что-то похожего на greatest/least с учетом null? Нагуглить сходу не удалось.

Ну… можно написать свои функции, я такие себе собираю, когда нужно быстро мигрировать с ORACLE с сохранением бизнес логики с наименьшими потерями


К примеру в данном случае можно использовать что-то навроде такого


CREATE OR REPLACE FUNCTION f_array_has_null (ANYARRAY)
  RETURNS bool LANGUAGE sql IMMUTABLE AS
 'SELECT array_position($1, NULL) IS NOT NULL';

CREATE FUNCTION f_least_ora(VARIADIC arr numeric[])
  RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$
    BEGIN
      IF f_array_has_null($1) THEN
        RETURN NULL;
      ELSE
        RETURN (SELECT min(x) FROM unnest($1) x);
      END IF;
    END
$$ ;

CREATE FUNCTION f_greatest_ora(VARIADIC arr numeric[])
  RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$
    BEGIN
      IF f_array_has_null($1) THEN
        RETURN NULL;
      ELSE
        RETURN (SELECT max(x) FROM unnest($1) x);
      END IF;
    END
$$ ;

Ну и как результат


SELECT
    least(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55)          -- -15
  , f_least_ora(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55)    -- NULL
  , least(1, 42, -3, 0, -0.5, -15, 55)                      -- -15
  , f_least_ora(1, 42, -3, 0, -0.5, -15, 55)                -- NULL
  , greatest(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55)       -- 55 
  , f_greatest_ora(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55) -- NULL
  , greatest(1, 42, -3, 0, -0.5, -15, 55)                   -- 55
  , f_greatest_ora(1, 42, -3, 0, -0.5, -15, 55)             -- 55

А вот за абзац про экранирование строки через $$ вам от меня благодарность! Я писал функции и не понимал, что просто описываю тело функции в виде обычного текстового поля в ddl команде create function .... as $$ ... $$. По факту я могу смело писать


do language plpgsql 'begin select 1; end';

вместо идущего в примерах


do language plpgsql $$begin select 1; end$$;

ведь это одно и то же.

~ — это сокращенная запись LIKE?
Мне казалось, тильда делает поиск по регулярному выражению, а LIKE только учитывает проценты/черточки

Согласен, неверно выразился. Я имел в виду утверждение, что LIKE '%text%' выдаст такой же результат, что ~ 'text'
А разве не две тильды соответствует LIKE? То есть LIKE '%text%' = ~~'%text%'
С одной тильдой во-первых не будет работать, во-вторых в исходниках представлений LIKE автоматически заменяется на две тильды, ILIKE со звёздочкой соответственно.
Если есть NULLIF, то можно добавить и COALESCE, выбирает первое значение отличное от NULL.
SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4, 5); -- 1
Добавил COALESCE и еще парочку
Ну, думаю, COALESCE знаком всем, кто плотно работает с PostgreSQL. Конструкции вида WHERE COALESCE(field, 0) = 0 и подобные встречаются повсеместно, если тип поля допускает NULL. А на разовых запросах позволяет не вспоминать, что там за поле что оно допускает.
Там же «курс молодого бойца», а не опытного ;-)
Кстати, я стараюсь не допускать идентичности NULL и 0 (NULL и ''). Так удобнее, чтобы пусто было пусто, а 0 может что-то значить. В WHERE, соответственно пишу FieldValue IS NULL. А при заполнении полей сначала привожу переменные _variable:=NULLIF(_variable,'') или в триггере причёсываю значения полей.
Кстати, я стараюсь не допускать идентичности NULL и 0

Да все стараются, как вы сказали, «с опытом». NULL разрешается только там, где он логически необходим, а на практике это не так уж и часто.
Но структуры достаются по наследству, либо сам не очень удачно спроектировал и т.д., когда на рефакторинг ресурсов нет, имеем что имеем…
Ещё хотелось бы добавить команду DO — выполнение анонимного блока кода. Бывает полезно когда нужно разово (по-быстрому) выполнить какие-то действия в транзакции, без создания отдельной функции.

DO $$
DECLARE 
  -- переменные
BEGIN
  -- блок кода
  -- * транзакция запускается автоматически
  -- * для вывода данных удобно использовать RAISE NOTICE 'Data: %', foo;
END$$;
Зачем в 8 примере в запросе используются UNION ALL? С помощью VALUES можно выбрать сразу несколько строк:
SQL
WITH company (id,c_name) AS (
VALUES (1, 'ООО РОМАШка'),
(2, 'ООО "РОМАШКА"'),
(3, 'ООО РаМАШКА'),
(4, 'ОАО "РОМАКША"'),
(5, 'ЗАО РОМАШКА'),
(6, 'ООО РО МАШКА'),
(7, 'ООО РОГА И КОПЫТА'),
(8, 'ZAO РОМАШКА'),
(9, 'Как это сюда попало?'),
(10, 'Ромашка 33'),
(11, 'ИП "РомаШкович"'),
(12, 'ООО "Рома Шкович"'),
(13, 'ИП "Рома Шкович"')
)
SELECT *, similarity(c_name, 'ООО "РОМАШКА"')
,dense_rank() OVER (ORDER BY similarity(c_name, 'ООО "РОМАШКА"') DESC) 
AS "Ранжирование результатов" -- оконная функций, о ней будет сказано ниже
FROM company
WHERE similarity(c_name, 'ООО "РОМАШКА"') >0.25 -- значения от 0 до 1, чем ближе к 1, тем точнее совпадение
ORDER BY similarity DESC;

Напомню, что моя цель была показать как можно больше рабочих моментов.
Хотелось затронуть свойство UNION ALL. Согласен, там было бы уместнее SELECT вместо VALUES, но в примере 4 я показал, что можно через запятую VALUES перечислять.

Еще пара полезных штук:
1) Например вы используете составной ключ, и вам надо найти некоторые строки IN может работать с несколькими колонками:


SELECT * FROM product_attribute WHERE (product_id, attribute_id) IN ((1, 11), (2, 12), (2, 13))

2) Конструкцию VALUES удобно иногда использовать в FROM и JOINах:


SELECT tmp.dig, tmp.name FROM (VALUES (1, 'one'), (2, 'two'), '3, 'three')) as tmp(dig, name)
Кстати, если в postgtes-е cte и материализуется, то в других СУБД это вовсе не обязательно, т.е. я бы не стал рассматривать cte как что-то типа временной таблицы на время выполнения запроса всегда и везде.
Уважаемые знатоки postgres, есть небольшая задачка, подскажите как правильно решить её с помощью postgres(сейчас использую свою коленнописную фурту на python для такого расчета).
Вообщем задачка:
Есть простая таблица с колонками id и price. В данных колонка записываются результаты примерно в таком порядке:
id price
1 45
2 80
3 75
4 125
5 50
6 70
7 22
8 23
9 47
10 20
Необходимо: отобразить id всех колонок, где (price)значение или сума сложения которых(-ой) будут равны, например, = 125
В приведенном случае это будут колонки: 4, 1+2, 3+5, 3+8+9 и так в порядке усложнения.
Подскажите пожалуйста более правильный вариант.
Заранее благодарен сообществу.
Автору спасибо большое за статью.

Что то я сомневаюсь что это задача уровня бд


PS 3+8+9 = 145 :)

Да, немного подошибся:)
Простым перебором как-то так, спасибо habrahabr.ru/post/340460/#comment_10497126



SQL
DROP TABLE IF EXISTS My_Values;
CREATE  TABLE My_Values (id,sm) --Создаем таблицу с данными
as ( 
VALUES (1, 45),
(2, 80),
(3 ,75),
(4, 125),
(5, 50),
(6, 70),
(7, 22),
(8, 23),
(9, 47),
(10, 20)
);

DROP TABLE IF EXISTS need_val;
CREATE  TABLE need_val(nv) 
AS SELECT 125; --- УКАЗЫВАЕМ НАШЕ ЧИСЛО
--p.s. Большие числа с мелкими составными будут очень долго вычисляться

WITH RECURSIVE t AS (
	WITH q as (
		SELECT DISTINCT
		 id
		, sm
		FROM My_Values
		WHERE sm <= (SELECT nv FROM need_val) and sm >0
		
	) 
	SELECT id,
	sm,
	sm tsm,
	id::text AS path,
	sm::text AS vals
	FROM q
	UNION ALL
	SELECT 
	q.id AS id,
	q.sm AS sm,
	t.tsm + q.sm AS tsm,
	(t.path || ','::text) || q.id AS path,
	(t.vals || '+'::text) || q.sm AS vals
	FROM t
	JOIN q ON q.id < t.id 
	--where (t.tsm + q.sm) <= (SELECT nv FROM need_val)
	)
SELECT t.path, tsm,'='||t.vals
FROM t
WHERE t.tsm = (SELECT nv FROM need_val)
--LIMIT 10000;

А в VALUE указать построчное соответствие/скормить таблицу с колонками id и price вместо перечисления нельзя? Каждый раз id и price меняются, количество строк over 1000, переписывать эти параметры врукопашную задача так скажем не из простых.
Большое Вам спасибо за проявленный интерес к моему вопросу.
Можно скормить табличку, или view на 2 столбца этой таблицы. Отпишите как по скорости будет работать.

Повесьте триггер на инсерт и пересчитывайте. Результаты можно в отдельнкю таблицу. Вариантов вобщем то миллион

Тригер не подходит. По скольку мне нужно делать данные вычитывания только тогда, когда есть не плановое событие.
Примерный алгоритм вам ниже подсказали, а так PL/pgSQL поддерживает циклы FOR, WHILE, и даже FOREACH. Вот и вперёд :)
Очень похоже на одну из вариаций задачи о Рюкзаке (Knapsack problem) Wiki


Прикольное решение, с разрешения автора zoroda выкладываю немного доработанный вариант.
Конечно, рекурсивный перебор не эффективный, но результат не так плох как я думал.
Например: нашел все варианты как можно получить число 195 из суммы чисел кратных трем.


SQL побаловаться
DROP TABLE IF EXISTS My_Values;
CREATE  TABLE My_Values (id,sm) --Создаем таблицу с данными
AS (SELECT row_number() over(),* FROM generate_series(0,500,3) ORDER BY 2 DESC );

DROP TABLE IF EXISTS need_val;
CREATE  TABLE need_val(nv) 
AS SELECT 195; --- УКАЗЫВАЕМ НАШЕ ЧИСЛО
--p.s. Большие числа с мелкими составными будут очень долго вычисляться

WITH RECURSIVE t AS (
	WITH q as (
		SELECT DISTINCT
		 id
		, sm
		FROM My_Values
		WHERE sm <= (SELECT nv FROM need_val) and sm >0
		
	) 
	SELECT id,
	sm,
	sm tsm,
	id::text AS path,
	sm::text AS vals
	FROM q
	UNION ALL
	SELECT 
	q.id AS id,
	q.sm AS sm,
	t.tsm + q.sm AS tsm,
	(t.path || ','::text) || q.id AS path,
	(t.vals || '+'::text) || q.sm AS vals
	FROM t
	JOIN q ON q.id < t.id 
	where (t.tsm + q.sm) <= (SELECT nv FROM need_val)
	)
SELECT t.path, tsm,'='||t.vals
FROM t
WHERE t.tsm = (SELECT nv FROM need_val)
--LIMIT 10000;

Ох, еще вспомнил, что с to_date(text, text) надо быть осторожным, так как PostgreSQL спокойно скушает to_date('30.02.2017', 'dd.mm.yyyy') и вернет 2 марта 2017, когда Oracle и DB2 LUW вернет ошибку.


В таком случае, если позволяет задача переключится на другой datestyle и сделать простое приведение типов


sql> set datestyle to DMY
sql> SELECT '30.02.2017'::date
[22008] ERROR: date/time field value out of range: "30.02.2017" Position: 8
Зачем этот неудобный pgadmin, когда есть в 100 раз лучше dbForge for PostgreSQL. Думаю, скоро и студия появится.
Как минимум потому, что он полностью бесплатный…
И для тривиальных задач хватает, не все же тут разработчики.
… и не кроссплатформенный.
array_to_string(array_agg(any_val),';') можно сократить до string_agg(any_val,';')

А еще, планировщик с большей вероятностью использует индекс по t2_id в
SELECT * FROM table1 WHERE t2_id=ANY((SELECT array_agg(id) FROM table2)::integer[])
чем в
SELECT * FROM table1 WHERE t2_id IN (SELECT id FROM table2)
в зависимости от объема записей в table2
string_agg() крут, спасибо. Жаль его не было до 9 версии.

Насчет оператора IN. Давно заметил, что его лучше не использовать, если IN (OVER 9000 rows..)
и обычно переписывал на EXISTS или JOIN
SELECT * FROM table1 t1 
WHERE EXISTS (SELECT 1 FROM table2 t2 where t2.id = t1.t2_id)

Спасибо за: "=ANY((SELECT array_agg(id) FROM table2)::integer[])" — интересная особенность.
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.