How to become an author
Search
Write a publication
Pull to refresh

Comments 28

В первом примере почему сразу по PK не выбирать с NOT EXISTS? Такое и в других базах будет работать.
Total votes 1: ↑1 and ↓0+1
Если ситуация позволяет, то конечно можно. Но мне приходилось столкнуться на практике, когда надо было сравнить именно сами данные. Т.е. есть одна таблица, есть другая, новая, с той же структурой. И надо было сравнить, что именно изменилось, какие данные в каких строках. Т.е. с одинаковыми pk могли быть разные значения других полей
Total votes 4: ↑4 and ↓0+4
Эх, если бы Postgres поддерживал MINUS…

UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.
Total votes 1: ↑1 and ↓0+1
UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.
У всех, кроме «Оракла», это называется EXCEPT.
Total votes 5: ↑5 and ↓0+5
Спасибо, не знал. Ну, значит не зря написал статью )
This comment wasn’t rated yet0
VALUES можно использовать везде, где и SELECT, очень помогает для работы с кучей значений. Например, для расшифровки численных значений. Что-то типа:
WITH sv(sex, value) AS (
     VALUES(0, 'мужской'), (1, 'женский'), (2, 'не знаю')
)
SELECT fullname, sv.value FROM "user" INNER JOIN sv USING(sex)
Total votes 7: ↑7 and ↓0+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;


чтоб уж совсем прочувствовать всю мощь )
Total votes 1: ↑1 and ↓0+1
А чем это лучше CASE (который работает везде)?

SELECT fullname
     , CASE WHEN value = 0 THEN 'мужской'
            WHEN value = 1 THEN 'женский'
            WHEN value = 2 THEN 'вы в толерантной стране'
       ELSE 1 -- что-то мы не все предусмотрели
       END sex_ru
FROM "user"
Total votes 1: ↑1 and ↓0+1
Тем, что в запросе может быть несколько таких мест, а указать можно только в одном.
This comment wasn’t rated yet0
Так в исходном запросе для этого и используется CTE, что мешает перенести CASE в него?
This comment wasn’t rated yet0
Исходный запрос — это всего лишь упрощённые пример, чтобы показать суть. Даже странно такие вещи растолковывать.
Total votes 1: ↑1 and ↓0+1
Оператор Except имхо более подходящий для примера 1.
И работать должен во всех БД
Total votes 2: ↑2 and ↓0+2
Я счас после пива и БД нет под рукой, но
Select * from table1
Where f1, f2, f3 not in ( select * from table2)
Тоже должно работать, если во второй таблице соответствующие поля
Total votes 2: ↑0 and ↓2-2
В Oracle вместо него используется MINUS. Работает так же.
This comment wasn’t rated yet0
В функциях если входной параметр может быть 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;
Total votes 2: ↑1 and ↓10
Часто бывает нужно сравнить данные на двух разных базах по 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 организовать то же самое
This comment wasn’t rated yet0
Еще очень мощная вещь оконные функции. Во была хорошая статейка на тему habrahabr.ru/post/268983
Total votes 2: ↑2 and ↓0+2
Это моя же статья. Решил здесь не повторяться
Total votes 4: ↑4 and ↓0+4
Пожалуйста, напишите, что вы используете в повседневной работе. Что-нибудь такое, что возможно не для всех очевидно

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

UPD. Я буду читать все комменты, прежде чем оставить свой…
This comment wasn’t rated yet0
Выбрать дублирующиеся значения f2 вместе c pk этих записей

select f2, count(id) cnt, array_agg(id) from table
group by f2
having count(id) > 1
order by cnt desc;
Total votes 1: ↑1 and ↓0+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;
Total votes 1: ↑1 and ↓0+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).
This comment wasn’t rated yet0
Удаление геометрических дублей

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));
Total votes 3: ↑3 and ↓0+3
Sign up to leave a comment.

Articles

Show the best of all time
Change theme settings

Your account

Sections

Information

Services

Support
© 2006–2024, Habr