update - Insertar, en la actualización duplicada en PostgreSQL?

postgresql 9.5 upsert example

Question

Hace varios meses aprendí de una respuesta en Stack Overflow cómo realizar varias 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 he cambiado a PostgreSQL y al parecer esto no es correcto. Se refiere a todas las tablas correctas, por lo que asumo que se trata de diferentes palabras clave que se utilizan, pero no estoy seguro de qué parte de la documentación de PostgreSQL está cubierta.

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

Answer 1

ACTUALIZACIÓN devolverá el número de filas modificadas. Si usa JDBC (Java), puede verificar este valor contra 0 y, si no hay filas afectadas, puede disparar INSERT. Si usa algún otro lenguaje de programación, tal vez el número de filas modificadas aún se pueda obtener, verifique la documentación.

Puede que esto no sea tan elegante, pero tiene un SQL mucho más simple que es más trivial de usar desde el código de llamada. De manera diferente, si escribe un guión de diez líneas en PL / PSQL, probablemente debería tener una prueba unitaria de uno u otro tipo solo para ello.

Answer 2

Con PostgreSQL 9.1 esto se puede lograr usando un CTE grabable ( 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:

• Upserting via Escrable CTE
• ESPERANDO 9.1 - CTE ESCRITABLE
• ¿POR QUÉ EL PEDIDO ES TAN COMPLICADO?

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

Answer 3

De acuerdo con la documentación de PostgreSQL de la INSERT , no se admite el manejo del caso de la ON DUPLICATE KEY . Esa parte de la sintaxis es una extensión propietaria de MySQL.

Answer 4

En PostgreSQL 9.5 y versiones posteriores puede usar INSERT ... ON CONFLICT UPDATE .

Consulte la documentación .

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

por ejemplo, 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 de 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 especificar el nombre de la columna (o el nombre de la restricción única) que se usará para la verificación de la unicidad. Esa es la ON CONFLICT (columnname) DO

• Se debe usar la palabra clave SET , como si se UPDATE instrucción UPDATE normal

También tiene algunas características agradables:

• Puede tener una cláusula WHERE en su UPDATE (lo que le permite activar efectivamente la ON CONFLICT UPDATE ON CONFLICT IGNORE 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 será 10 (porque eso es lo que intentamos insertar) y "table".c será 3 porque ese es el valor actual en la tabla. Puede usar una o las dos expresiones SET y la cláusula WHERE .

Para obtener más información sobre upsert, consulte Cómo poner un mensaje UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) en PostgreSQL?

Answer 5

Estaba buscando lo mismo cuando vine aquí, pero la falta de una función genérica "upsert" 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

que 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, "upsert" por lotes, podría usar Tcl para dividir sql_update y realizar un bucle de las actualizaciones individuales, el golpe de rendimiento será muy pequeño, consulte 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

Answer 6

No hay un comando simple para hacerlo.

El enfoque más correcto es utilizar la función, como la de los docs .

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

Algo a lo largo de las líneas de:

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 aquí hay una clara condición de carrera, pero generalmente funcionará.

Aquí hay un artículo más largo y más completo sobre el tema .

Answer 7

Para combinar conjuntos pequeños, usar la función anterior está bien. Sin embargo, si está fusionando grandes cantidades de datos, sugeriría consultar http://mbk.projects.postgresql.org

La mejor práctica actual que conozco es:

1. COPIAR datos nuevos / actualizados en la tabla temporal (seguro, o puede hacer INSERTAR si el costo es correcto)
2. Adquirir bloqueo [opcional] (es preferible avisar a los bloqueos de tablas, OMI)
3. Unir. (la parte divertida)

Answer 8

Personalizo la función "upsert" de arriba, si desea 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 luego de ejecutar, haz algo como esto:

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

Es importante poner doble coma-dólar para evitar errores de compilación.

• comprueba la velocidad ...

Answer 9

Personalmente, he configurado una "regla" adjunta a la declaración de inserción. Supongamos que tenía una tabla "dns" que registraba las visitas dns por cliente en función del tiempo:

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

Quería poder reinsertar filas con valores actualizados o crearlos si aún no existían. Tecleado 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 se producen inserciones simultáneas, ya que generará excepciones de infracción única. Sin embargo, la transacción no terminada continuará y tendrá éxito, y solo necesita repetir la transacción terminada.

Sin embargo, si ocurren toneladas de inserciones todo el tiempo, querrá poner un bloqueo de tabla alrededor de las declaraciones de inserción: el bloqueo COMPARTIR DE LA FILA 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 realiza ninguna operación, utilice en su lugar bloqueos de advertencia.

Además, el comando COPIAR no usa REGLAS, por lo tanto, si está insertando con COPIA, tendrá que usar disparadores en su lugar.

Answer 10

PostgreSQL desde la versión 9.5 tiene sintaxis UPSERT , con la cláusula ON CONFLICT . 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;

Buscar en los archivos del grupo de correo electrónico de postgresql para "upsert" lleva a encontrar un ejemplo de lo que posiblemente desee hacer, en el manual :

Ejemplo 38-2. Excepciones con ACTUALIZAR / INSERTAR

Este ejemplo utiliza 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'');

Es posible que haya un ejemplo de cómo hacer esto de forma masiva, utilizando CTE en 9.1 y superiores, en la 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;

Vea la respuesta de a_horse_with_no_name para un ejemplo más claro.

Answer 11

Similar a la respuesta más apreciada, 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/ )

Answer 12

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

Mi solución, similar a JWP, consiste en borrar y reemplazar en masa, 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 aproximadamente 20 configuraciones por cliente, son solo 3 llamadas de db de carga bastante baja, probablemente el método más rápido.

La alternativa de actualizar filas individuales, verificar las excepciones y luego insertarlas, o alguna combinación de ellas es un código horrible, lento y a menudo se rompe porque (como se mencionó anteriormente) el manejo de excepciones de 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

Answer 13

Yo uso esta función fusionar

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

Answer 14

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

Otra forma inteligente de hacer un "UPSERT" en postgresql es hacer dos sentencias UPDATE / INSERT secuenciales 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 tiene efecto.

El INSERT solo tendrá éxito 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 ejecutada desde su aplicación. Se recomienda encarecidamente ejecutarlos juntos en una sola transacción.

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 podría fallar con un error de clave duplicada si una fila se inserta al mismo tiempo, o podría terminar sin una fila insertada cuando se elimina una fila al mismo tiempo . Una transacción SERIALIZABLE en PostgreSQL 9.1 o superior lo manejará de manera confiable al costo de una tasa de fallas de serialización muy alta, lo que significa que tendrá que volver a intentarlo mucho. Ver por qué es tan complicado el aumento , que trata este caso con más detalle.

Este enfoque también está sujeto a la pérdida de actualizaciones en el aislamiento de read committed menos que la aplicación verifique los recuentos de filas afectadas y verifique que el insert o la update afectado a una fila .

Answer 15

Editar: Esto no funciona como se esperaba. A diferencia de la respuesta aceptada, esto produce violaciones de claves únicas cuando dos procesos llaman repetidamente a upsert_foo mismo tiempo.

¡Eureka! Descubrí una forma de hacerlo en una consulta: use UPDATE ... RETURNING para probar si alguna fila fue afectada:

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

La UPDATE se debe realizar en un procedimiento separado porque, desafortunadamente, este es un error de sintaxis:

... WHERE NOT EXISTS (UPDATE ...)

Ahora funciona como se desea:

SELECT upsert_foo(1, ''hi'');
SELECT upsert_foo(1, ''bye'');
SELECT upsert_foo(3, ''hi'');
SELECT upsert_foo(3, ''bye'');

Answer 16

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