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');

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

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

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

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

• Вконтакте ()
• Google+
• Facebook ( )