Pregunta Insertar, en la actualización duplicada en PostgreSQL?


Hace varios meses aprendí de una respuesta en Stack Overflow cómo realizar múltiples actualizaciones a la vez en MySQL usando la siguiente sintaxis:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

Ahora me he pasado a PostgreSQL y aparentemente esto no es correcto. Se refiere a todas las tablas correctas, así que supongo que es una cuestión de palabras clave diferentes que se utilizan, pero no estoy seguro de qué parte de la documentación de PostgreSQL está cubierto.

Para aclarar, quiero insertar varias cosas y si ya existen para actualizarlas.


519
2017-07-10 11:38


origen


Respuestas:


PostgreSQL desde la versión 9.5 tiene UPSERT sintaxis, con EN CONFLICTO cláusula. con la siguiente sintaxis (similar a MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

La búsqueda de los archivos del grupo de correos electrónicos de postgresql para "rescatar" lleva a encontrar un ejemplo de hacer lo que posiblemente quiera hacer, en el manual:

Ejemplo 38-2. Excepciones con ACTUALIZACIÓN / INSERCIÓN

Este ejemplo usa el manejo de excepciones para realizar UPDATE o INSERT, según corresponda:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

Posiblemente haya un ejemplo de cómo hacer esto a granel, usando CTE en 9.1 y superior, en el lista de correo de hackers:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

Ver a_horse_with_no_name's answer para un ejemplo más claro.


355
2017-07-10 12:18



Advertencia: esto no es seguro si se ejecuta desde varias sesiones al mismo tiempo (ver advertencias a continuación).


Otra manera ingeniosa de hacer un "UPSERT" en postgresql es hacer dos instrucciones secuenciales de ACTUALIZACIÓN / INSERCIÓN que están diseñadas para tener éxito o no tienen ningún efecto.

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

La ACTUALIZACIÓN tendrá éxito si ya existe una fila con "id = 3", de lo contrario no tendrá ningún efecto.

El INSERT tendrá éxito solo si la fila con "id = 3" aún no existe.

Puede combinar estos dos en una sola cadena y ejecutarlos con una sola instrucción SQL ejecutándose desde su aplicación. Ejecutarlos juntos en una sola transacción es muy recomendable.

Esto funciona muy bien cuando se ejecuta de forma aislada o en una tabla bloqueada, pero está sujeto a condiciones de carrera que significa que aún puede fallar con un error clave duplicado si se inserta una fila al mismo tiempo, o puede terminar sin insertar fila cuando se elimina una fila al mismo tiempo . UN SERIALIZABLE la transacción en PostgreSQL 9.1 o superior lo manejará de manera confiable a costa de una tasa de fallas de serialización muy alta, lo que significa que tendrá que volver a intentar mucho. Ver ¿Por qué es tan complicado?, que analiza este caso con más detalle.

Este enfoque es también sujeto a actualizaciones perdidas en read committed aislamiento a menos que la aplicación verifique los recuentos de filas afectadas y verifique que el insert o el update afectó una fila.


404
2018-06-29 22:06



Con PostgreSQL 9.1 esto se puede lograr usando un CTE escribible (expresión de tabla común)

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

Ver estas entradas de blog:


Tenga en cuenta que esta solución no no prevenir una violación de clave única, pero no es vulnerable a las actualizaciones perdidas.
Ver el seguimiento por Craig Ringer en dba.stackexchange.com


215
2018-01-02 15:00



En PostgreSQL 9.5 y más reciente puedes usar INSERT ... ON CONFLICT UPDATE.

Ver la documentación.

A MySQL INSERT ... ON DUPLICATE KEY UPDATE puede ser reformulado directamente a un ON CONFLICT UPDATE. Tampoco es la sintaxis estándar de SQL, ambas son extensiones específicas de la base de datos. Hay buenas razones MERGE no fue usado para esto, una nueva sintaxis no fue creada solo por diversión. (La sintaxis de MySQL también tiene problemas que significan que no fue adoptada directamente).

p.ej. configuración dada:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

la consulta MySQL:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

se convierte en:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Diferencias

  • debe especifique el nombre de columna (o el nombre de restricción único) que se utilizará para la comprobación de unicidad. Eso es ON CONFLICT (columnname) DO

  • La palabra clave SET debe usarse, como si esto fuera una UPDATE declaración

También tiene algunas características agradables:

  • Puedes tener un WHERE cláusula en su UPDATE (Dejándole efectivamente girar ON CONFLICT UPDATE dentro ON CONFLICT IGNORE para ciertos valores)

  • Los valores propuestos para la inserción están disponibles como la variable de fila EXCLUDED, que tiene la misma estructura que la tabla de destino. Puede obtener los valores originales en la tabla utilizando el nombre de la tabla. Entonces en este caso EXCLUDED.c estarán 10 (porque eso es lo que tratamos de insertar) y "table".c estarán 3 porque ese es el valor actual en la tabla. Puede usar una o ambas en el SET expresiones y WHERE cláusula.

Para el fondo en upsert ver ¿Cómo UPSERT (FUSIÓN, INSERTAR ... EN ACTUALIZACIÓN DUPLICADA) en PostgreSQL?


97
2018-05-08 07:53



Estaba buscando lo mismo cuando llegué aquí, pero la falta de una función genérica de "recuperación" me molestó un poco, así que pensé que podría pasar la actualización e insertar SQL como argumentos en esa función del manual

eso se vería así:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

y tal vez para hacer lo que inicialmente quería hacer, lote "upsert", podría usar Tcl para dividir el sql_update y repetir las actualizaciones individuales, el hit de preformance será muy pequeño ver http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

el costo más alto es ejecutar la consulta desde su código, en el lado de la base de datos, el costo de ejecución es mucho menor


16
2017-09-16 16:13



No hay un comando simple para hacerlo.

El enfoque más correcto es usar la función, como la de documentos.

Otra solución (aunque no tan segura) es actualizar con la devolución, verificar qué filas son actualizaciones e insertar el resto de ellas

Algo como:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

asumiendo id: 2 fue devuelto:

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Por supuesto, se rescatará tarde o temprano (en un entorno concurrente), ya que hay una clara condición de carrera aquí, pero por lo general funcionará.

Aquí está un Artículo más extenso y extenso sobre el tema.


12
2017-07-10 12:04



Personalmente, he configurado una "regla" adjunta a la declaración de inserción. Supongamos que tiene una tabla de "DNS" que registra los hits de DNS por cliente por tiempo:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

Quería poder volver a insertar filas con valores actualizados, o crearlos si no existían ya. Clave en el customer_id y el tiempo. Algo como esto:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Actualización: Esto tiene el potencial de fallar si están ocurriendo insertos simultáneos, ya que generará excepciones únicas de infracción. Sin embargo, la transacción no finalizada continuará y tendrá éxito, y solo necesita repetir la transacción terminada.

Sin embargo, si hay toneladas de insertos suceden todo el tiempo, deseará colocar un bloqueo de tabla alrededor de las instrucciones de inserción: SHARE ROW El bloqueo EXCLUSIVO evitará cualquier operación que pueda insertar, eliminar o actualizar filas en su tabla de destino. Sin embargo, las actualizaciones que no actualizan la clave única son seguras, por lo que si no hay ninguna operación que lo haga, utilice en su lugar cerraduras de aviso.

Además, el comando COPY no utiliza REGLAS, por lo tanto, si está insertando con COPY, deberá usar activadores en su lugar.


8
2018-05-10 23:18



Personalizo la función "upsert" arriba, si quiere INSERTAR Y REEMPLAZAR:

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

Y después de ejecutar, haz algo como esto:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

Es importante poner doble coma dólar para evitar errores del compilador

  • verifica la velocidad ...

7
2017-09-21 20:55



Tengo el mismo problema para administrar la configuración de la cuenta como pares de nombre y valor. El criterio de diseño es que diferentes clientes podrían tener diferentes conjuntos de configuraciones.

Mi solución, similar a JWP, es borrar y reemplazar a granel, generando el registro de fusión dentro de su aplicación.

Esto es bastante a prueba de balas, independiente de la plataforma y dado que nunca hay más de 20 configuraciones por cliente, estas son solo 3 llamadas de carga de baja carga, probablemente el método más rápido.

La alternativa de actualizar filas individuales -verificando excepciones y luego insertando- o una combinación de código horrible, lento y, a menudo, rompe porque (como se mencionó anteriormente) el manejo de excepciones SQL no estándar cambia de db a db, o incluso de lanzamiento a lanzamiento.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

5
2017-10-21 06:11



Similar a la respuesta más querida, pero funciona un poco más rápido:

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(fuente: http://www.the-art-of-web.com/sql/upsert/)


5
2017-07-29 14:48



CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT

4
2017-12-29 10:50