database - ¿Cómo emular "insertar ignorar" y "en actualización de clave duplicada"(fusión de sql) con postgresql?

rules

Question

¿Cuál es la mejor forma de emular "insertar ignorar" y "actualizar la clave duplicada" con postgresql?

Answer 1

A granel, siempre puede eliminar la fila antes de la inserción. Una eliminación de una fila que no existe no causa un error, por lo que se omite de forma segura.

Answer 2

Como @hanmari mencionó en su comentario. cuando se inserta en una tabla de postgres, el conflicto en (..) no hacer nada es el mejor código para usar para no insertar datos duplicados .:

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

La línea de código ON CONFLICT permitirá a la instrucción insertar insertar filas de datos. El código de consultas y valores es un ejemplo de fecha insertada de un Excel en una tabla db postgres. Tengo restricciones añadidas a una tabla de postgres que uso para asegurarme de que el campo ID sea único. En lugar de ejecutar un borrado en las filas de datos que son iguales, agrego una línea de código sql que renumera la columna ID a partir de 1. Ejemplo:

q = ''ALTER id_column serial RESTART WITH 1''

Si mis datos tienen un campo de Id., No los utilizo como identificación principal / ID de serie, creo una columna de ID y la configuro en serie. Espero que esta información sea útil para todos. * No tengo un título universitario en desarrollo / codificación de software. Todo lo que sé sobre codificación, estudio por mi cuenta.

Answer 3

Con PostgreSQL 9.5, esta es ahora la funcionalidad nativa (como MySQL ha tenido durante varios años):

INSERTAR ... EN CONFLICTO NO HACER NADA / ACTUALIZAR ("ALERTAR")

9.5 brinda soporte para operaciones "UPSERT". INSERT se extiende para aceptar una cláusula ON CONFLICT DO UPDATE / IGNORE. Esta cláusula especifica una acción alternativa a tomar en el caso de una supuesta violación duplicada.

...

Ejemplo adicional de nueva sintaxis:

INSERT INTO user_logins (username, logins)
VALUES (''Naomi'',1),(''James'',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

Answer 4

Editar: en caso de que hayas olvidado la respuesta de warren , PG9.5 ahora tiene esto de forma nativa; hora de actualizar!

Basándose en la respuesta de Bill Karwin, para deletrear cómo se vería un enfoque basado en reglas (transferencia desde otro esquema en el mismo DB, y con una clave primaria de múltiples columnas):

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

Nota: La regla se aplica a todas las operaciones INSERT hasta que se INSERT la regla, por lo que no es muy ad hoc.

Answer 5

Esta solución evita el uso de reglas:

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

pero tiene un inconveniente de rendimiento (ver PostgreSQL.org ):

Un bloque que contiene una cláusula EXCEPTION es significativamente más caro para entrar y salir que un bloque sin uno. Por lo tanto, no use EXCEPTION sin necesidad.

Answer 6

Intenta hacer una ACTUALIZACIÓN. Si no modifica ninguna fila, lo que significa que no existió, haz una inserción. Obviamente, haces esto dentro de una transacción.

Por supuesto, puede ajustar esto en una función si no desea poner el código adicional en el lado del cliente. También necesita un ciclo para la rara condición de carrera en ese pensamiento.

Hay un ejemplo de esto en la documentación: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html , ejemplo 40-2 justo en la parte inferior.

Esa es generalmente la forma más fácil. Puedes hacer algo de magia con las reglas, pero es probable que sea mucho más complicado. Recomiendo el enfoque de envoltura en función sobre eso cualquier día.

Esto funciona para valores de fila única o de pocas filas. Si está tratando con grandes cantidades de filas, por ejemplo, desde una subconsulta, lo mejor es dividirlo en dos consultas, una para INSERT y otra para UPDATE (como una combinación / subselección apropiada del curso, sin necesidad de escribir su principal filtrar dos veces)

Answer 7

Para aquellos de ustedes que tienen Postgres 9.5 o superior, la nueva sintaxis ON CONFLICT DO NOTHING debería funcionar:

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

Para aquellos de nosotros que tenemos una versión anterior, esta combinación correcta funcionará en su lugar:

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

Answer 8

Para las secuencias de comandos de importación de datos, para reemplazar "SI NO EXISTE", de alguna manera, hay una formulación un tanto incómoda que, sin embargo, funciona:

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;

IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;

Answer 9

Para que la inserción ignore la lógica, puede hacer algo como a continuación. Descubrí que simplemente insertando desde una declaración seleccionada de valores literales funcionaba mejor, luego puedes enmascarar las claves duplicadas con una cláusula NOT EXISTS. Para obtener la actualización de la lógica duplicada, sospecho que un bucle pl / pgsql sería necesario.

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  (''935'','' Citroën Brazil'',''Citroën''),
  (''ABC'', ''Toyota'', ''Toyota''),
  (''ZOM'','' OM'',''OM'')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

Answer 10

Parece que PostgreSQL admite un objeto de esquema llamado regla .

http://www.postgresql.org/docs/current/static/rules-update.html

Puede crear una regla ON INSERT para una tabla determinada, haciendo que NOTHING si existe una fila con el valor de clave primaria dado, o haciendo que UPDATE vez de INSERT si existe una fila con el valor de clave primaria dado.

No lo he intentado yo mismo, así que no puedo hablar por experiencia u ofrecer un ejemplo.

Answer 11

INSERT INTO mytable(col1,col2) 
    SELECT ''val1'',''val2'' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1=''val1'')