Комментарии 59
Думаю, с приведением типов любой разработчик, использующий postgres рано или поздно столкнется, а вот про функции, подобные similarity жду следующий пост.
similarity и для меня была 'палочкой выручалочкой', грустно, когда и не предполагаешь, что СУБД может такое 'вытворять'.
Я бы с удовольствием выпустил «Часть 2», знать бы в сторону каких функций и фишек смотреть…
Процедурный язык PL/pgSQL — www.postgresql.org/docs/current/static/plpgsql.html
Я считаю, что на это стоит обратить внимание. Я бы сказал, что без использования этих вещей postgresql и не postgresql вовсе.
Откройте для себя товарища Левенштейна https://postgrespro.ru/docs/postgresql/9.4/fuzzystrmatch.html
Я вырезал участок своего негодования из статьи, т.к. он даже не совпадал с документацией postgres 8, а перепроверив тело функции, я понял свою ошибку.
В спойлере можно почерпнуть разницу между similarity и levenshtein
![](https://habrastorage.org/web/04a/949/a02/04a949a02a2843d184b9d3ab6fe9784f.jpg)
Изменилось направление сортировки (теперь 1 это полное совпадение) (функция лишь была так описана), перестали учитываться знаки пунктуации и регистр, ушло ограничение в 255 символов. Одно мне не понравилось, что на моей текущей версии Postgres 9.2 выдало 2 строки с похожестью 1, ибо одна из них не является полностью идентичной (может это к лучшему). Возможно, есть дополнительные настройки, но я не углублялся в это т.к. не было необходимости.
P.S. Только жаль, что SOUNDEX не очень подходит для русского языка.
Правда у меня объём был 230к, зато реальных данных. И связка очень красиво так получилась! Адреса разной структуры — в одном опущена область, в другом индекс, первый структурированный с разделителем, второй ручной ввод.
Естественно некий процент ошибок будет, но это лучше, чем всё лопатить руками.
Проверяет соответствие регулярному выражению с учётом регистра
LIKE использует не регулярные выражения, а свой синтаксис шаблонов. Кто это знает, понял, что имелось в виду, а кто не знает, мог подумать, что туда надо писать regexp.
Как альтернативу временным таблицам могу предложить переменные типа jsonb или array композитных типов, правда индексы тут уже не применить, в отличие от временных таблиц. К слову, jsonb делает работу с данными в plpgsql гораздо удобнее, рекомендую взять на вооружение.
CREATE INDEX station_synonyms_idx
ON station
USING btree
((additional_data #>> '{synonyms}'::text[]) COLLATE pg_catalog."default");
Это индекс по текстовому массиву. Думаю, нет смысла приводить примеры для простых строковых или числовых индексов.
Но если в переменной большой массив dict-ов, то как из него вытащить пару значений по каким то условиям, не перебрав его целиком? Индексы на переменные типа jsonb не повешать.
dblink — не функция, а расширение. Странно, что всплыло именно оно, а не postgres_fdw: https://www.postgresql.org/docs/9.6/static/postgres-fdw.html
Для удалённого вызова функций удобнее использовать 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
На скорую руку можно обойтись таким костылём:
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 только учитывает проценты/черточки
SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4, 5); -- 1
WHERE COALESCE(field, 0) = 0
и подобные встречаются повсеместно, если тип поля допускает NULL. А на разовых запросах позволяет не вспоминать, что там за поле что оно допускает.Кстати, я стараюсь не допускать идентичности NULL и 0 (NULL и ''). Так удобнее, чтобы пусто было пусто, а 0 может что-то значить. В WHERE, соответственно пишу FieldValue IS NULL. А при заполнении полей сначала привожу переменные _variable:=NULLIF(_variable,'') или в триггере причёсываю значения полей.
Кстати, я стараюсь не допускать идентичности NULL и 0
Да все стараются, как вы сказали, «с опытом». NULL разрешается только там, где он логически необходим, а на практике это не так уж и часто.
Но структуры достаются по наследству, либо сам не очень удачно спроектировал и т.д., когда на рефакторинг ресурсов нет, имеем что имеем…
DO $$
DECLARE
-- переменные
BEGIN
-- блок кода
-- * транзакция запускается автоматически
-- * для вывода данных удобно использовать RAISE NOTICE 'Data: %', foo;
END$$;
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;
Еще пара полезных штук:
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)
Вообщем задачка:
Есть простая таблица с колонками 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 :)
![](https://habrastorage.org/webt/x-/3o/vd/x-3ovdxh0w64ssvmlz3isfzdckg.png)
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;
Большое Вам спасибо за проявленный интерес к моему вопросу.
Повесьте триггер на инсерт и пересчитывайте. Результаты можно в отдельнкю таблицу. Вариантов вобщем то миллион
Конечно, рекурсивный перебор не эффективный, но результат не так плох как я думал.
Например: нашел все варианты как можно получить число 195 из суммы чисел кратных трем.
![](https://habrastorage.org/webt/st/-t/dj/st-tdjysymixlnkkhlz1xno0hyk.png)
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
А еще, планировщик с большей вероятностью использует индекс по 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Насчет оператора 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[])" — интересная особенность.
Курс молодого бойца PostgreSQL