varanio 6 апр 2016 в 07:27

Полезные трюки PostgreSQL

3 мин

202K

PostgreSQL*SQL*Программирование*Веб-разработка*

+55

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

printercu 6 апр 2016 в 08:03

В первом примере почему сразу по PK не выбирать с NOT EXISTS? Такое и в других базах будет работать.

varanio 6 апр 2016 в 08:11

Если ситуация позволяет, то конечно можно. Но мне приходилось столкнуться на практике, когда надо было сравнить именно сами данные. Т.е. есть одна таблица, есть другая, новая, с той же структурой. И надо было сравнить, что именно изменилось, какие данные в каких строках. Т.е. с одинаковыми pk могли быть разные значения других полей

ComodoHacker 6 апр 2016 в 11:44

Эх, если бы Postgres поддерживал MINUS…

UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.

bolk 6 апр 2016 в 18:01

UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.

У всех, кроме «Оракла», это называется EXCEPT.

myz0ne 6 апр 2016 в 10:42

Есть же Except/intersect. www.postgresql.org/docs/7.4/static/sql-select.html#SQL-EXCEPT причём работает и в других базах.

varanio 6 апр 2016 в 10:43

Спасибо, не знал. Ну, значит не зря написал статью )

AlterEgo20 6 апр 2016 в 13:06

Если ссылаетесь на мануал постгрес, лучше использовать ссылку на текущуб версию:
www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT

Версия 7.4 слишком уж древняя.

zloyreznic 6 апр 2016 в 09:27

generate_series очень помогло

bolk 6 апр 2016 в 09:39

VALUES можно использовать везде, где и SELECT, очень помогает для работы с кучей значений. Например, для расшифровки численных значений. Что-то типа:

WITH sv(sex, value) AS (
     VALUES(0, 'мужской'), (1, 'женский'), (2, 'не знаю')
)
SELECT fullname, sv.value FROM "user" INNER JOIN sv USING(sex)

kshvakov 6 апр 2016 в 10:42

Действительно, или так:

with recursive hw(_array, i, r) as (

    values (array['H', 'e', 'l', 'l', 'o', ',', ' ', 'w', 'o', 'r', 'l', 'd', '!'], 1, '')
    union all
    select _array, i + 1, r || _array[i] from hw where i <= array_length(_array, 1)
)
select r as result from hw order by i desc limit 1;

чтоб уж совсем прочувствовать всю мощь )

kast218 12 апр 2016 в 11:39

А чем это лучше CASE (который работает везде)?

SELECT fullname
     , CASE WHEN value = 0 THEN 'мужской'
            WHEN value = 1 THEN 'женский'
            WHEN value = 2 THEN 'вы в толерантной стране'
       ELSE 1 -- что-то мы не все предусмотрели
       END sex_ru
FROM "user"

bolk 12 апр 2016 в 11:41

Тем, что в запросе может быть несколько таких мест, а указать можно только в одном.

kast218 12 апр 2016 в 11:44

Так в исходном запросе для этого и используется CTE, что мешает перенести CASE в него?

bolk 12 апр 2016 в 11:46

Исходный запрос — это всего лишь упрощённые пример, чтобы показать суть. Даже странно такие вещи растолковывать.

tamakio 6 апр 2016 в 10:08

Оператор Except имхо более подходящий для примера 1.
И работать должен во всех БД

varanio 6 апр 2016 в 10:44

Ага, спасибо

tamakio 6 апр 2016 в 10:47

Я счас после пива и БД нет под рукой, но
Select * from table1
Where f1, f2, f3 not in ( select * from table2)
Тоже должно работать, если во второй таблице соответствующие поля

potapuff 6 апр 2016 в 10:53

В Oracle вместо него используется MINUS. Работает так же.

ALHIMIK1992 6 апр 2016 в 10:42

В функциях если входной параметр может быть NULL
CREATE OR REPLACE FUNCTION get_text (date)
RETURNS text AS
$BODY$
DECLARE
on_date alias for $1;
rec text;
BEGIN
IF on_date IS NULL THEN
FOR rec IN SELECT some_text
FROM table_with_text
LOOP
RETURN NEXT rec;
END LOOP;
ELSE
FOR rec IN SELECT some_text
FROM table_with_text
WHERE date_text = on_date
LOOP
RETURN NEXT rec;
END LOOP;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Можно проверить на NULL сразу в WHERE
CREATE OR REPLACE FUNCTION get_text (date)
RETURNS text AS
$BODY$
DECLARE
on_date alias for $1;
rec text;
BEGIN
FOR rec IN SELECT some_text
FROM table_with_text
WHERE (on_date IS NULL OR date_text = on_date)
LOOP
RETURN NEXT rec;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

ALHIMIK1992 6 апр 2016 в 13:15

Теги не сработали(

Uranic2 6 апр 2016 в 11:15

Часто бывает нужно сравнить данные на двух разных базах по id. Делаю через json и функции row_to_json и json_populate_recordset. Сначала получаю JSON на мастер базе:

SELECT json_agg(row_to_json(e.*))::json
FROM mm.action e
WHERE e.id IN (7, 8)
— А потом на другой базе

UPDATE
mm.action nnn
SET
name = fff.name,
def_rght_id = fff.def_rght_id,
action_type_id = fff.action_type_id,
img16_index = fff.img16_index,
img32_index = fff.img32_index,
param = fff.param
FROM json_populate_recordset(NULL::mm.action,
/* здесь JSON, полученный на первом шаге */
) fff
WHERE nnn.id=fff.id

На любителя можно через hstore организовать то же самое

shorokhovs 6 апр 2016 в 11:15

Еще очень мощная вещь оконные функции. Во была хорошая статейка на тему habrahabr.ru/post/268983

varanio 6 апр 2016 в 11:16

Это моя же статья. Решил здесь не повторяться

AlexLeonov 6 апр 2016 в 11:35

Пожалуйста, напишите, что вы используете в повседневной работе. Что-нибудь такое, что возможно не для всех очевидно

Оконные функции

UPD. Я буду читать все комменты, прежде чем оставить свой…

myz0ne 6 апр 2016 в 14:34

Выбрать дублирующиеся значения f2 вместе c pk этих записей

select f2, count(id) cnt, array_agg(id) from table
group by f2
having count(id) > 1
order by cnt desc;

myz0ne 6 апр 2016 в 14:48

Выбрать день из поля с типом timestamp. Удобно для быстрого подсчета статистики по дням
to_char(time, 'YYYY-MM-DD') as day.
Например:
select now(), to_char(now(), 'YYYY-MM-DD') as day;

Немного админской магии:

Показать привелегии пользователей
SELECT grantee, table_schema || '.' || table_name AS relname, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
--WHERE grantee = 'user'
GROUP BY grantee, relname,table_name;

Показать размер таблицы/индекса.
select pg_size_pretty(pg_relation_size('schema.table|index'));

Размер всех таблиц по убыванию. external_table_usage — размер индексов, итд.
SELECT
schemaname||'.'||tablename AS full_tname,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_usage,
pg_size_pretty((pg_total_relation_size(schemaname||'.'||tablename) — pg_relation_size(schemaname||'.'||tablename))) AS external_table_usage
FROM pg_catalog.pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC

Размер всех индексов по убыванию
SELECT
schemaname||'.'||indexname AS full_tname,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||indexname)) AS usage
FROM pg_catalog.pg_indexes
ORDER BY pg_total_relation_size(schemaname||'.'||indexname) DESC;

restop 7 апр 2016 в 09:43

Последнее время часто использую composite types

create type my_type as (
id integer,
can_be_updated boolean
);

использовать можно, например, в функциях:
CREATE OR REPLACE FUNCTION find_pattern(… )
RETURNS my_type AS
$body$
declare
my_record my_type;
begin

SELECT id, case when state = 0 then 'Y' else 'N' end
INTO my_record
FROM my_table
WHERE type = 'my_type'

if not found then
my_record.id = null;
end if;

return my_record;

end;

Еще недавно оказалась полезна функция split_part(string text, delimiter text, field int) — делает сплит строки по разделителю и возвращает указанный элемент массива(индекс от 1).

lokks 7 апр 2016 в 16:13

Удаление геометрических дублей

DELETE FROM data.table
WHERE EXISTS(SELECT id FROM data.table c WHERE c.id < table.id AND c.geom && table.geom AND ST_Equals(table.geom, c.geom));

Зарегистрируйтесь на Хабре, чтобы оставить комментарий