How to use INSERT ON CONFLICT to overwrite data - AnalyticDB - Centro de documentación de Alibaba Cloud

INSERT ON CONFLICT realiza una operación de upsert: inserta una fila cuando no existe ningún conflicto, o actualiza la fila existente si se detecta un conflicto de clave primaria o índice único. Esto evita fallos de escritura por conflictos de clave primaria durante la sincronización de datos o importaciones masivas. Esta función es similar a la instrucción REPLACE INTO de MySQL. Este tema describe la sintaxis de INSERT ON CONFLICT y proporciona ejemplos de uso.

Limitaciones

Tablas particionadas: Compatible solo con instancias que ejecutan la versión menor V6.3.6.1 o posterior.

Consulte la versión menor de su instancia en la Basic Information de la consola de AnalyticDB for PostgreSQL. Para actualizarla, consulte Update the minor version of an instance .
Tipos de tabla compatibles: Solo tablas Heap y tablas Beam. Las tablas columnares (AO/AOCS) no admiten índices únicos, por lo que no son compatibles.
Claves duplicadas en una sola instrucción: Una única instrucción INSERT no puede incluir varias filas con la misma clave primaria. Se trata de una restricción del estándar SQL.

Sintaxis

Sintaxis básica

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

Sintaxis completa

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action can be:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

Parámetros

Parámetro	Descripción
`conflict_target`	Especifica qué constituye un conflicto. Para `DO NOTHING`: opcional; si se omite, se comprueban todas las restricciones e índices únicos utilizables. Para `DO UPDATE`: obligatorio; debe identificar la clave primaria o el índice único que desencadena el conflicto.
`conflict_action`	Especifica la acción a tomar cuando se detecta un conflicto. `DO NOTHING` descarta la fila propuesta. `DO UPDATE` actualiza la fila existente mediante la cláusula `DO UPDATE SET`; para hacer referencia a los valores de la fila propuesta, utilice la pseudotabla `excluded`. `DO UPDATE ALL` realiza una actualización de toda la fila y es obligatorio para las tablas Beam.

Restricciones de DO UPDATE:

No se puede actualizar la clave de distribución ni la clave primaria en la cláusula SET.
La cláusula WHERE no admite subconsultas.
Beam tables no admiten actualizaciones parciales de columnas; en su lugar, utilice DO UPDATE ALL para actualizaciones de fila completa.

Ejemplos

Los ejemplos utilizan una tabla t1 con las columnas a int PRIMARY KEY, b int, c int, d int DEFAULT 0.

Configuración de la tabla de prueba

Cree la tabla t1:

CREATE TABLE t1 (
  a int PRIMARY KEY,
  b int,
  c int,
  d int DEFAULT 0
);

(Solo tablas Beam) Si utiliza el motor de almacenamiento Beam y necesita actualizaciones parciales de columnas en caso de conflicto, cambie el método de acceso de la tabla a heap:
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
Inserte una fila inicial:
```
INSERT INTO t1 VALUES (0,0,0,0);
```

Compruebe los datos:

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

El problema de conflicto

Una instrucción INSERT estándar con una clave primaria duplicada falla:

INSERT INTO t1 VALUES (0,1,1,1);

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

La cláusula ON CONFLICT gestiona esta situación al ignorar la fila en conflicto o actualizarla.

Ignorar la fila en conflicto

Utilice DO NOTHING para omitir la inserción cuando ocurre un conflicto. La fila existente permanece sin cambios.

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

Actualizar todas las columnas que no son clave primaria en caso de conflicto

Utilice DO UPDATE SET para sobrescribir la fila existente. La pseudotabla excluded contiene los valores de la inserción propuesta.

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

Alternativamente, especifique cada columna individualmente:

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT (a)
DO UPDATE SET
  b = excluded.b, c = excluded.c, d = excluded.d;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)

Actualizar columnas específicas en caso de conflicto

Para actualizar solo un subconjunto de columnas, incluya únicamente esas columnas en SET. Las columnas no listadas conservan sus valores actuales.

Primero restablezca los datos de prueba:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Sobrescribir solo la columna c:

INSERT INTO t1
VALUES (0,3,3,3)
ON CONFLICT (a)
DO UPDATE SET
  c = excluded.c;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 row)

Incrementar una columna según su valor actual:

INSERT INTO t1
VALUES (0,0,1,0)
ON CONFLICT (a)
DO UPDATE SET
  c = t1.c + 1;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 row)

Restablecer una columna a su valor predeterminado en caso de conflicto

Utilice DEFAULT en SET para restaurar una columna a su valor predeterminado definido.

Primero restablezca los datos de prueba:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

INSERT INTO t1
VALUES (0,0,2,2)
ON CONFLICT (a)
DO UPDATE SET
  d = DEFAULT;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 0
(1 row)

La columna d se restablece a 0 (su valor predeterminado), mientras que b y c permanecen sin cambios.

Insertar o actualizar varias filas

Una sola instrucción INSERT puede insertar o actualizar varias filas. Cada fila se evalúa de forma independiente según la directiva de conflicto.

Primero restablezca los datos de prueba:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Omitir filas en conflicto e insertar filas nuevas:

INSERT INTO t1
VALUES (0,2,2,2), (3,3,3,3)
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)

La fila (0,2,2,2) se omite porque a = 0 ya existe. La fila (3,3,3,3) se inserta normalmente.

Actualizar filas en conflicto e insertar filas nuevas:

INSERT INTO t1
VALUES (0,0,0,0), (4,4,4,4)
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)

Insertar o actualizar desde una subconsulta

INSERT ON CONFLICT funciona con INSERT INTO ... SELECT para combinar datos entre tablas. Esto resulta útil para pipelines ETL y flujos de trabajo de fusión de tablas.

Restablezca los datos de prueba:

DELETE FROM t1 WHERE a != 0;

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Cree una tabla de origen e inserte datos:

CREATE TABLE t2 (LIKE t1);

INSERT INTO t2 VALUES (0,11,11,11), (2,22,22,22);

Combine t2 en t1. En caso de conflicto, sobrescriba las columnas que no son clave primaria:

INSERT INTO t1
SELECT * FROM t2
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

SELECT * FROM t1;

 a | b  | c  | d
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(2 rows)

Actualización de fila completa para tablas Beam

Las tablas Beam no admiten actualizaciones parciales de columnas. Utilice DO UPDATE ALL para actualizar toda la fila en conflicto.

Cree una tabla Beam:

CREATE TABLE beam_test (
  a int PRIMARY KEY,
  b int,
  c int,
  d int DEFAULT 0
) USING beam;

Inserte datos iniciales:

INSERT INTO beam_test VALUES (0,0,0,0), (1,1,1,1), (2,2,2,2);

Compruebe los datos:

SELECT * FROM beam_test;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)

Inserte o actualice una fila en conflicto mediante DO UPDATE ALL:

INSERT INTO beam_test
VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE ALL;

Compruebe el resultado:

SELECT * FROM beam_test;

 a | b | c | d
---+---+---+---
 0 | 4 | 4 | 4
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)

Preguntas frecuentes

¿Cómo compruebo el motor de almacenamiento de una tabla?

Consulte los catálogos del sistema pg_class y pg_am. Reemplace schemaname.tablename por el nombre completo de su tabla:

SELECT
  c.oid::regclass AS rel,
  coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schemaname.tablename'::regclass
  AND c.relkind = 'r';

¿Por qué mi tabla Beam falla con "DO UPDATE SET is not supported for beam relations"?

Problema: Al realizar una actualización parcial mediante INSERT ON CONFLICT en una tabla Beam, se produce el siguiente error:

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

El motor de almacenamiento Beam solo admite actualizaciones de fila completa. Reemplace DO UPDATE SET ... por DO UPDATE ALL:

-- Before (fails on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

-- After (works on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE ALL;