¿Cómo hacer UPSERT (FUSIONAR, INSERTAR … EN LA ACTUALIZACIÓN DUPLICADA) en PostgreSQL?

Mantén la atención porque en este post vas a encontrar el hallazgo que buscas.

Solución:

9.5 y más reciente:

PostgreSQL 9.5 y soporte más reciente INSERT ... ON CONFLICT (key) DO UPDATE (y ON CONFLICT (key) DO NOTHING), es decir, upsert.

Comparar con ON DUPLICATE KEY UPDATE.

Explicación rápida.

Para su uso, consulte el manual, específicamente el conflicto_accion cláusula en el diagrama de sintaxis y el texto explicativo.

A diferencia de las soluciones para 9.4 y anteriores que se proporcionan a continuación, esta función funciona con varias filas en conflicto y no requiere bloqueo exclusivo o un bucle de reintento.

El compromiso que agrega la función está aquí y la discusión sobre su desarrollo está aquí.

---

Si tiene 9.5 y no necesita ser compatible con versiones anteriores, puede dejar de leer ahora.

---

9.4 y mayores:

PostgreSQL no tiene ningún UPSERT (o MERGE), y hacerlo de manera eficiente frente al uso concurrente es muy difícil.

Este artículo analiza el problema con detalles útiles.

En general debes elegir entre dos opciones:

• Operaciones individuales de inserción / actualización en un bucle de reintento; o
• Bloquear la mesa y hacer una combinación por lotes

Bucle de reintento de fila individual

El uso de inserciones de filas individuales en un bucle de reintento es la opción razonable si desea que muchas conexiones intenten realizar inserciones al mismo tiempo.

La documentación de PostgreSQL contiene un procedimiento útil que le permitirá hacer esto en un bucle dentro de la base de datos. Protege contra actualizaciones perdidas y carreras de inserción, a diferencia de la mayoría de las soluciones ingenuas. Solo funcionará en READ COMMITTED sin embargo, solo es seguro si es lo único que hace en la transacción. La función no funcionará correctamente si se activa o si es secundaria única keys causar infracciones únicas.

Esta estrategia es muy ineficaz. Siempre que sea práctico, debe hacer cola para el trabajo y hacer un upsert masivo como se describe a continuación.

Muchos intentos de solución a este problema no tienen en cuenta las reversiones, por lo que dan como resultado actualizaciones incompletas. Dos transacciones compiten entre sí; uno de ellos con éxito INSERTs; el otro obtiene un duplicado key error y hace un UPDATE en lugar de. los UPDATE bloques esperando el INSERT para deshacer o confirmar. Cuando retrocede, el UPDATE la verificación de condición coincide con cero filas, por lo que aunque el UPDATE confirma que en realidad no ha hecho el upsert que esperabas. Debe verificar el recuento de filas de resultados y volver a intentarlo cuando sea necesario.

Algunos intentos de solución tampoco tienen en cuenta las carreras SELECT. Si prueba lo obvio y simple:

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

luego, cuando dos se ejecutan a la vez, hay varios modos de falla. Uno es el problema ya discutido con una revisión de actualización. Otro es donde ambos UPDATE al mismo tiempo, haciendo coincidir cero filas y continuando. Entonces ambos hacen el EXISTS prueba, que sucede antes de los INSERT. Ambos obtienen cero filas, por lo que ambos hacen el INSERT. Uno falla con un duplicado key error.

Es por eso que necesita un bucle de reintento. Podría pensar que puede evitar la duplicación key errores o actualizaciones perdidas con SQL inteligente, pero no puede. Necesita verificar los recuentos de filas o manejar duplicados key errores (dependiendo del enfoque elegido) y vuelva a intentarlo.

Por favor, no desarrolle su propia solución para esto. Al igual que con la cola de mensajes, probablemente esté mal.

Postre a granel con cerradura

A veces, desea hacer un upsert masivo, donde tiene un nuevo conjunto de datos que desea fusionar en un conjunto de datos existente más antiguo. Este es vastamente más eficiente que las inserciones de hileras individuales y debe preferirse siempre que sea práctico.

En este caso, normalmente sigue el siguiente proceso:

• CREATE a TEMPORARY mesa

• COPY o inserte de forma masiva los nuevos datos en la tabla temporal

• LOCK la mesa de destino IN EXCLUSIVE MODE. Esto permite que otras transacciones SELECT, pero no realizar cambios en la tabla.

• Hacer un UPDATE ... FROM de registros existentes usando los valores de la tabla temporal;

• Hacer un INSERT de filas que aún no existen en la tabla de destino;

• COMMIT, liberando el bloqueo.

Por ejemplo, para el ejemplo dado en la pregunta, el uso de valores múltiples INSERT para poblar la tabla temporal:

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer, somedata text);

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;

Lectura relacionada

• Página wiki de UPSERT
• UPSERTisms en Postgres
• Insertar, ¿en la actualización duplicada en PostgreSQL?
• http://petereisentraut.blogspot.com/2010/05/merge-syntax.html
• Upsert con una transacción
• ¿Es SELECT o INSERT en una función propensa a las condiciones de carrera?
• SQL MERGE en la wiki de PostgreSQL
• La forma más idiomática de implementar UPSERT en Postgresql hoy en día

Qué pasa MERGE?

Estándar SQL MERGE en realidad tiene una semántica de concurrencia mal definida y no es adecuado para upserting sin bloquear primero una tabla.

Es una declaración OLAP realmente útil para la combinación de datos, pero en realidad no es una solución útil para actualizaciones seguras de concurrencia. Hay muchos consejos para que las personas que utilizan otros DBMS utilicen MERGE para upserts, pero en realidad está mal.

Otras bases de datos:

• INSERT ... ON DUPLICATE KEY UPDATE en MySQL
• MERGE desde MS SQL Server (pero consulte más arriba sobre MERGE problemas)
• MERGE de Oracle (pero vea arriba sobre MERGE problemas)

A continuación se muestran algunos ejemplos de insert ... on conflict ... (pág 9.5+):

• Insertar, en caso de conflicto – hacer nada.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict do nothing;`  
  

• Insertar, en caso de conflicto – actualizar, especifique el objetivo del conflicto a través de columna.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict(id)
  do update set name = 'new_name', size = 3;  
  

• Insertar, en caso de conflicto – actualizar, especifique el objetivo del conflicto a través de nombre de restricción.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict on constraint dummy_pkey
  do update set name = 'new_name', size = 4;
  

Estoy tratando de contribuir con otra solución para el problema de inserción única con las versiones anteriores a 9.5 de PostgreSQL. La idea es simplemente intentar realizar primero la inserción, y en caso de que el registro ya esté presente, actualizarlo:

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

Tenga en cuenta que esta solución se puede aplicar solo si no hay eliminaciones de filas de la tabla.

No conozco la eficacia de esta solución, pero me parece bastante razonable.

---

---

Utiliza Nuestro Buscador