Комментарии 28
В первом примере почему сразу по PK не выбирать с NOT EXISTS? Такое и в других базах будет работать.
+1
Если ситуация позволяет, то конечно можно. Но мне приходилось столкнуться на практике, когда надо было сравнить именно сами данные. Т.е. есть одна таблица, есть другая, новая, с той же структурой. И надо было сравнить, что именно изменилось, какие данные в каких строках. Т.е. с одинаковыми pk могли быть разные значения других полей
+4
Есть же Except/intersect. www.postgresql.org/docs/7.4/static/sql-select.html#SQL-EXCEPT причём работает и в других базах.
+4
Спасибо, не знал. Ну, значит не зря написал статью )
0
Если ссылаетесь на мануал постгрес, лучше использовать ссылку на текущуб версию:
www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT
Версия 7.4 слишком уж древняя.
www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT
Версия 7.4 слишком уж древняя.
0
generate_series очень помогло
+1
VALUES можно использовать везде, где и SELECT, очень помогает для работы с кучей значений. Например, для расшифровки численных значений. Что-то типа:
WITH sv(sex, value) AS (
VALUES(0, 'мужской'), (1, 'женский'), (2, 'не знаю')
)
SELECT fullname, sv.value FROM "user" INNER JOIN sv USING(sex)
+7
Действительно, или так:
чтоб уж совсем прочувствовать всю мощь )
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;
чтоб уж совсем прочувствовать всю мощь )
+1
А чем это лучше CASE (который работает везде)?
SELECT fullname
, CASE WHEN value = 0 THEN 'мужской'
WHEN value = 1 THEN 'женский'
WHEN value = 2 THEN 'вы в толерантной стране'
ELSE 1 -- что-то мы не все предусмотрели
END sex_ru
FROM "user"
+1
Оператор Except имхо более подходящий для примера 1.
И работать должен во всех БД
И работать должен во всех БД
+2
В функциях если входной параметр может быть 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;
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;
0
Часто бывает нужно сравнить данные на двух разных базах по 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 организовать то же самое
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 организовать то же самое
0
Еще очень мощная вещь оконные функции. Во была хорошая статейка на тему habrahabr.ru/post/268983
+2
Пожалуйста, напишите, что вы используете в повседневной работе. Что-нибудь такое, что возможно не для всех очевидно
Оконные функции
UPD. Я буду читать все комменты, прежде чем оставить свой…
0
Выбрать дублирующиеся значения f2 вместе c pk этих записей
select f2, count(id) cnt, array_agg(id) from table
group by f2
having count(id) > 1
order by cnt desc;
select f2, count(id) cnt, array_agg(id) from table
group by f2
having count(id) > 1
order by cnt desc;
+1
Выбрать день из поля с типом 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;
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;
+1
Последнее время часто использую 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).
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).
0
Удаление геометрических дублей
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));
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));
+3
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Полезные трюки PostgreSQL