Merge на PostgreSQL (9.1)

Тестировал на PostgreSQL версии 9.1. На версии 8.4 запрос отказывается работать.

Перед нами стоит задача обновить значения в таблицах измерений. Для примера выберем dim_test.

Первое, что приходит на ум - это использование функции merge. Однако для PostgreSQL функция не реализована.
Решение задачи - использование конструкции CTE для обновления таблиц измерений (обновление через PDI рассмотрим отдельно).


Какие допущения будем предполагать:

  1. Во входящей таблице измерений из OLTP систем есть уникальный индекс (назовем таблицу dim_test_source).
  2. Нас не интересует как таблица (dim_test_source) попадает в нашу БД (можно использовать PDI, для перекидывания).

Вобщем схема  такая:

Схема таблиц

Подготовка таблиц

Зададим таблицу dim_test:

CREATE SEQUENCE dim_test_seq;
CREATE TABLE dim_test
(
  id_dim_test bigint NOT NULL DEFAULT nextval('dim_test_seq'::regclass),
  id bigint,
  name text,
  last_name text,
  CONSTRAINT id_dim_test_pk PRIMARY KEY (id_dim_test)
);
 ALTER SEQUENCE dim_test_seq OWNED BY dim_test.id_dim_test ;
CREATE INDEX idx_dim_test_tk
  ON dim_test
  USING btree
  (id_dim_test);

 

id_dim_test - это суррогатный ключ таблицы dim_test или в как встречается в литературе технический ключ.
id - это уникальный ключ dim_test_source.

Создаем входную таблицу dim_test_source:

CREATE SEQUENCE dim_test_source_seq;
CREATE TABLE dim_test_source
(
  id bigint NOT NULL DEFAULT nextval('dim_test_source_seq'),
  name text,
  last_name text,
  CONSTRAINT id_dim_source_pk PRIMARY KEY (id)
)
;
 ALTER SEQUENCE dim_test_source_seq OWNED BY dim_test_source.id ;

 Заполняем ее данными:

INSERT INTO dim_test_source("name", last_name) VALUES ( 'first','test_1');
INSERT INTO dim_test_source("name", last_name) VALUES ( 'second','test_1');
INSERT INTO dim_test_source("name", last_name) VALUES ( 'third','test_1');

Запрос на обновление 

Итак запрос обновления будет следующим:

WITH update_dim as
(
UPDATE dim_test 
    set "name"=dim_test_source."name", 
    last_name=dim_test_source.last_name 
from dim_test_source where dim_test.id=dim_test_source.id
  RETURNING dim_test_source.id
)
insert into dim_test ( id, "name", last_name) 
select dim_test_source.id, 
       dim_test_source."name", 
       dim_test_source.last_name 
from dim_test_source left join update_dim USING(id) 
where update_dim.id isnull;

 update_dim - таблица состоящая из ключей, у которых обновили запись:

  1. WITH - конструкция, которая позволяет выполнять повторяющиеся запросы. Мы будем выполнять запрос UPDATE dim_test set "name"=dim_test_source."name" ...для каждого id таблицы dim_test_source.
  2. Создает таблицу обновленных ключей конструкция RETURNING dim_test_source.id
  3. Вставляем ключи, у которых нет еще записей. Надо объединить две таблицы. Первая таблица - входная dim_test_source, а вторая update_dim - собранная при помощи RETURNING dim_test_source.id. Используем для объединения left join ( dim_test_source left join update_dim USING(id)) и условие проверки на пустые ключи WHERE update_dim.id isnull
  4. Конструкция USING(id) эквивалентна ON dim_test_source.id = update_dim.id

Далее, обновим пару записей в таблице dim_test_source:

UPDATE dim_test_source    SET  name='third_V2'  WHERE id = 3;
UPDATE dim_test_source    SET  last_name='test_2'  WHERE id = 2;

 И добавим данных:

INSERT INTO dim_test_source("name", last_name) VALUES ( 'fourth','test_1');
INSERT INTO dim_test_source("name", last_name) VALUES ( 'fifth','test_1');

 Запускаем запрос на обновление еще раз и получаем следующий результат.

result

Обновление прошло успешно.

Ссылки на материалы:

  1. PostgreSQL 9.1: эмуляция MERGE с помощью Writable CTE http://dfndr.name/content/postgresql-91-%D1%8D%D0%BC%D1%83%D0%BB%D1%8F%D1%86%D0%B8%D1%8F-merge-%D1%81-%D0%BF%D0%BE%D0%BC%D0%BE%D1%89%D1%8C%D1%8E-writable-cte
  2. UPSERT/MERGE using Writable CTE in PostgreSQL 9.1 http://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/
  3. Получение id добавленной записи в PostgeSQL http://habrahabr.ru/post/72590/
  4. Batch Update http://tapoueh.org/blog/2013/03/15-batch-update.html