Вопрос:
Используя Postgres, я могу выполнить оператор update и возвращать строки, затронутые commend.
UPDATE accounts SET status = merge_accounts.status, field1 = merge_accounts.field1, field2 = merge_accounts.field2, etc. FROM merge_accounts WHERE merge_accounts.uid =accounts.uid RETURNING accounts.*
Это даст мне список всех записей, сопоставляемых с предложением WHERE, однако не скажет мне, какие строки были фактически обновлены операцией.
В этом упрощенном случае использования, конечно, было бы тривиально просто добавить еще один охранник AND status != ‘Closed, однако мой реальный случай использования в действительности включает в себя обновление потенциально десятков полей из таблицы слияния с более чем 10 000 строк, и я хочу быть в состоянии определить, какие строки были фактически изменены, и которые идентичны их предыдущей версии. (Ожидание очень мало строк на самом деле изменилось).
Лучшее, что у меня осталось, – это
UPDATE accounts SET x=…, y=… FROM accounts as old WHERE old.uid = accounts.uid FROM merge_accounts WHERE merge_accounts.uid = accounts.uid RETURNING accounts, old
Который вернет кортеж старых и новых строк, которые затем могут быть разбросаны внутри моей Java-кодовой базы, но это требует значительного дополнительного сетевого трафика и потенциально подвержено ошибкам.
Идеальный сценарий заключается в том, чтобы иметь возможность возвращать postgres только строки, на которых фактически были изменены значения – возможно ли это?
Здесь на github – более реальный пример того, что я делаю, включая некоторые из предложений.
Использование Postgres 9.1, но при необходимости может использовать 9.4. Требования эффективно
- Уметь выполнять обновление новых данных
- Где мы можем знать только конкретную пару ключ/значение для обновления в любой заданной строке
- Вернуть результат, содержащий только строки, которые были фактически изменены с помощью upsert
- Бонус – получите копию старых записей.
Поскольку этот вопрос был открыт, я получил большую часть этой работы сейчас, хотя я не уверен, что мой подход – хорошая идея или нет – он немного взломан.
Лучший ответ:Содержание
- Обновление только тех строк, которые фактически меняются.
- Сокращение для целых строк
- Для вашего особого случая
- Возвращает старое и новое значение:
Обновление только тех строк, которые фактически меняются.
Это сохраняет дорогие обновления и дорогих чеков после UPDATE.
Чтобы обновить каждый столбец с предоставленным новым значением (если что-либо изменится):
UPDATE accounts a SET (status, field1, field2) — short syntax for .. = (m.status, m.field1, m.field2) — .. updating multiple columns FROM merge_accounts m WHERE m.uid = a.uid AND (a.status IS DISTINCT FROM m.status OR a.field1 IS DISTINCT FROM m.field1 OR a.field2 IS DISTINCT FROM m.field2) RETURNING a.*;
Из-за модели PostgreSQL MVCC любое изменение строки записывает новую версию строки. Обновление одного столбца почти так же дорого, как обновление каждого столбца в строке сразу. Переписывание остальной части строки происходит практически без затрат, как только вам нужно что-то обновить.
Подробнее:
- Как мне (или я могу) выбрать SELECT DISTINCT в нескольких столбцах?
- ОБНОВЛЯЙТЕ целую строку в PL/pgSQL
Сокращение для целых строк
Если типы строк accounts и merge_accounts идентичны, и вы хотите перенести все из merge_accounts в accounts, есть ярлык, сравнивающий весь тип строки:
UPDATE accounts a SET (status, field1, field2) = (m.status, m.field1, m.field2) FROM merge_accounts m WHERE a.uid = m.uid AND m IS DISTINCT FROM a RETURNING a.*;
Это даже работает для значений NULL. Подробности в руководстве.
Но это не будет работать для вашего домашнего решения, где (quote):
merge_accounts идентичен, за исключением того, что все столбцы non-pk являются типами массивов
Требуется, чтобы типы строк были совместимы, т.е. каждый столбец имеет один и тот же тип данных или существует, по меньшей мере, неявный листинг, зарегистрированный между двумя типами.
Для вашего особого случая
UPDATE accounts a SET (status, field1, field2) = (COALESCE(m.status[1], a.status) — default to original .. , COALESCE(m.field1[1], a.field1) — .. if m.column[1] IS NULL , COALESCE(m.field2[1], a.field2)) FROM merge_accounts m WHERE m.uid = a.uid AND (m.status[1] IS NOT NULL AND a.status IS DISTINCT FROM m.status[1] OR m.field1[1] IS NOT NULL AND a.field1 IS DISTINCT FROM m.field1[1] OR m.field2[1] IS NOT NULL AND a.field2 IS DISTINCT FROM m.field2[1]) RETURNING a.*
m.status IS NOT NULL работает, если столбцы, которые не должны обновляться, равны NULL в merge_accounts.
m.status <> ‘{}’, если вы используете пустые массивы.
m.status[1] IS NOT NULL охватывает обе опции.
Возвращает старое и новое значение:
Как Jayadevan опубликовал, я уже ответил на это раньше:
- Возврат значений UPDATE Column с использованием SQL Only – PostgreSQL версии
Ответ №1
если вы не полагаетесь на побочные эффекты обновления, обновите только записи, которые нужно изменить
UPDATE accounts SET status = merge_accounts.status, field1 = merge_accounts.field1, field2 = merge_accounts.field2, etc. FROM merge_accounts WHERE merge_accounts.uid =accounts.uid AND NOT (status IS NOT DISTINCT FROM merge_accounts.status AND field1 IS NOT DISTINCT FROM merge_accounts.field1 AND field2 IS NOT DISTINCT FROM merge_accounts.field2 ) RETURNING accounts.* Ответ №2
Я бы рекомендовал использовать таблицу information_schema.columns для динамического анализа столбцов, а затем использовать функции внутри функции plpgsql для динамического создания оператора UPDATE.
то есть. этот DDL:
create table foo ( id serial, val integer, name text ); insert into foo (val, name) VALUES (10, ‘foo’), (20, ‘bar’), (30, ‘baz’);
И этот запрос:
select column_name from information_schema.columns where table_name = ‘foo’ order by ordinal_position;
даст столбцы для таблицы в том порядке, в каком они были определены в таблице DDL.
По существу, вы должны использовать вышеприведенный SELECT в функции для динамического создания вашего оператора UPDATE путем повторения результатов вышеуказанного SELECT в FOR LOOP для динамического создания как SET, так и WHERE.
Ответ №3
Некоторая вариация этого?
SELECT * FROM old; id | val —-+—— 1 | 1 2 | 2 4 | 5 5 | 1 6 | 2 SELECT * FROM new; id | val —-+—— 1 | 2 2 | 2 3 | 2 5 | 1 6 | 1 SELECT * FROM old JOIN new ON old.id = new.id; id | val | id | val —-+——+—-+—— 1 | 1 | 1 | 2 2 | 2 | 2 | 2 5 | 1 | 5 | 1 6 | 2 | 6 | 1 (4 rows) WITH sel AS ( SELECT o.id , o.val FROM old o JOIN new n ON o.id=n.id ), upd AS ( UPDATE old SET val = new.val FROM new WHERE new.id=old.id RETURNING old.* ) SELECT * from sel, upd WHERE sel.id = upd.id AND sel.val <> upd.val; id | val | id | val —-+——+—-+—— 1 | 1 | 1 | 2 6 | 2 | 6 | 1 (2 rows)
Обратитесь SO ответ и прочитайте всю дискуссию.
