Массовое/пакетное обновление /upsert в PostgreSQL

Ответ 1

Я использовал 3 стратегии для пакетной транзакционной работы:

• Генерировать операторы SQL "на лету", объединять их с запятой, а затем отправлять заявления одним выстрелом. Я сделал до 100 вставок таким образом, и это было довольно эффективно (сделано против Postgres).
• JDBC имеет встроенные возможности пакетной обработки, если они настроены. Если вы создаете транзакции, вы можете сбросить свои JDBC-заявления, чтобы они совершали транзакции одним выстрелом. Эта тактика требует меньше вызовов в базе данных, поскольку все операторы выполняются в одной партии.
• Hibernate также поддерживает JDBC-пакет по линиям предыдущего примера, но в этом случае вы выполняете метод flush() для Hibernate Session, а не для основного JDBC-соединения. Он выполняет то же самое, что и JDBC-пакет.

Кстати, Hibernate также поддерживает стратегию дозирования при сборе коллекции. Если вы комментируете коллекцию с помощью @BatchSize, при извлечении ассоциаций Hibernate будет использовать IN вместо =, что приведет к меньшему количеству операторов SELECT для загрузки коллекций.

Ответ 2

Массовая вставка

Вы можете изменить объемную вставку из трех столбцов Ketema:

INSERT INTO "table" (col1, col2, col3)
  VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);

Это будет:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(array[11,21,31]), 
          unnest(array[12,22,32]), 
          unnest(array[13,23,33]))

Замена значений заполнителями:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(?), unnest(?), unnest(?))

Вам необходимо передать массивы или списки в качестве аргументов этого запроса. Это означает, что вы можете делать огромные вложенные вставки без выполнения конкатенации строк (и все ее гашения и опасности: sql-инъекция и процитирование ада).

Массовое обновление

PostgreSQL добавила расширение FROM в UPDATE. Вы можете использовать его следующим образом:

update "table" 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where "table".key = data_table.key;

В руководстве отсутствует хорошее объяснение, но есть пример в postgresql-admin рассылке. Я попытался рассказать об этом:

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp set age = data_table.age
from
(select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;

Есть также другие сообщения в StackExchange, объясняющие UPDATE...FROM.., используя предложение VALUES вместо подзапрос. Они могут быть легче прочитаны, но ограничены фиксированным числом строк.

Ответ 3

Массовые вставки могут быть выполнены следующим образом:

INSERT INTO "table" ( col1, col2, col3)
  VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );

Вставляет 3 строки.

Множественное обновление определяется стандартом SQL, но не реализовано в PostgreSQL.

Цитата:

"В соответствии со стандартом синтаксис списка столбцов должен содержать список из столбцов, которые должны быть назначены из одного выражения с оценкой строки, например суб-выбор:

Учетные записи UPDATE SET (contact_last_name, contact_first_name) =     (SELECT last_name, first_name FROM salesmen      WHERE salesmen.id = accounts.sales_id);

Ссылка: http://www.postgresql.org/docs/9.0/static/sql-update.html

Ответ 4

довольно быстро заселить json в набор записей (postgresql 9.3 +)

big_list_of_tuples = [
    (1, "123.45"),
    ...
    (100000, "678.90"),
]

connection.execute("""
    UPDATE mytable
    SET myvalue = Q.myvalue
    FROM (
        SELECT (value->>0)::integer AS id, (value->>1)::decimal AS myvalue 
        FROM json_array_elements(%s)
    ) Q
    WHERE mytable.id = Q.id
    """, 
    [json.dumps(big_list_of_tuples)]
)

Ответ 5

Отключите автосообщение и просто выполните одно завершение в конце. В простом SQL это означает выпуск BEGIN в начале и COMMIT в конце. Вам нужно будет создать функцию , чтобы выполнить фактическое обновление.