Uso de COPY ON CONFLICT para sobrescribir datos - AnalyticDB - Centro de documentación de Alibaba Cloud

Al importar datos a AnalyticDB for PostgreSQL mediante COPY, la operación falla si alguna fila entrante entra en conflicto con una restricción existente en la tabla. COPY ON CONFLICT extiende la instrucción estándar COPY FROM para gestionar estos conflictos sin interrumpir la importación; esto permite sobrescribir la fila conflictiva u omitirla de forma silenciosa.

COPY ON CONFLICT solo es compatible con instancias de AnalyticDB for PostgreSQL V6.0 que ejecutan la versión menor del motor 20210528 o posterior. Actualice su instancia a la última versión menor del motor antes de utilizar esta función. Para obtener instrucciones, consulte Update the minor engine version .

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Una instancia de AnalyticDB for PostgreSQL V6.0 que ejecute la versión menor del motor 20210528 o posterior
Una tabla heap con una clave primaria o una restricción única como destino de la importación

Limitaciones

La tabla de destino debe ser una tabla heap. Las tablas optimizadas para anexión (AO) no son compatibles porque carecen de índices únicos.
Las tablas particionadas solo son compatibles con la versión menor del motor 6.3.6.1 o posterior. Para consultar las instrucciones de actualización, véase Update the minor engine version.
La tabla de destino no puede ser una vista actualizable.
COPY ON CONFLICT solo admite la dirección COPY FROM. La dirección COPY TO no es compatible.
El parámetro CONFLICT index no es compatible. COPY ON CONFLICT siempre valida todas las restricciones de la tabla en lugar de un índice específico. Si se especifica un índice de conflicto, se produce el siguiente error:
```
ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
```
El parámetro UPDATE SET no es compatible. COPY ON CONFLICT DO UPDATE siempre sobrescribe todas las columnas de la fila en conflicto en lugar de un subconjunto. Si se especifica UPDATE SET, se produce el siguiente error:
```
ERROR:  COPY ON CONFLICT does NOT support UPDATE SET targets
```

Sintaxis

COPY table [(column [, ...])] FROM {'file' | STDIN}
     [ [WITH]
       [BINARY]
       [OIDS]
       [HEADER]
       [DELIMITER [ AS ] 'delimiter']
       [NULL [ AS ] 'null string']
       [ESCAPE [ AS ] 'escape' | 'OFF']
       [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
       [CSV [QUOTE [ AS ] 'quote']
            [FORCE NOT NULL column [, ...]]
       [FILL MISSING FIELDS]
       [[LOG ERRORS]
       SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
    [DO ON CONFLICT DO UPDATE | NOTHING]

La cláusula de resolución de conflictos al final determina el comportamiento cuando una fila entrante coincide con una restricción existente:

Cláusula	Comportamiento
`DO ON CONFLICT DO UPDATE`	Sobrescribe la fila existente con todas las columnas de los datos entrantes.
`DO ON CONFLICT DO NOTHING`	Omite la fila entrante. La fila existente se conserva sin cambios.

Ejemplo

A continuación se presenta un escenario completo que abarca la importación de datos, la generación de un conflicto de restricción y su resolución mediante cada cláusula.

Paso 1: Crear una tabla

CREATE TABLE NATION (
    N_NATIONKEY  INTEGER,
    N_NAME       CHAR(25),
    N_REGIONKEY  INTEGER,
    N_COMMENT    VARCHAR(152),
    PRIMARY KEY (N_NATIONKEY)
);

N_NATIONKEY actúa como clave primaria y garantiza la unicidad en todas las filas.

Paso 2: Importar los datos iniciales

COPY NATION FROM stdin;

Cuando aparezca el símbolo del sistema >>, introduzca las siguientes filas. Utilice un carácter de tabulación entre los valores de las columnas.

0	'ALGERIA'	0	'haggle. carefully final deposits detect slyly agai'
1	'ARGENTINA'	1	'al foxes promise slyly according to the regular accounts. bold requests alon'
2	'BRAZIL'	1	'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
3	'CANADA'	1	'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
\.

Compruebe la importación:

SELECT * FROM NATION;

Resultado esperado:

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           0 | 'ALGERIA'                 |           0 | ' haggle. carefully final deposits detect slyly agai'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
(4 rows)

Paso 3: Observar un error de conflicto

Si intenta insertar una fila con N_NATIONKEY = 0 (valor que ya existe) mediante una instrucción COPY estándar, la operación fallará:

COPY NATION FROM stdin;

Introduzca la siguiente fila (separada por tabulaciones):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

La operación falla:

ERROR:  duplicate key value violates unique constraint "nation_pkey"
DETAIL:  Key (n_nationkey)=(0) already exists.
CONTEXT:  COPY nation, line 1

Paso 4: Sobrescribir la fila en conflicto

Utilice DO ON CONFLICT DO UPDATE para reemplazar la fila existente con los datos entrantes. Cuando ocurre un conflicto, COPY ON CONFLICT DO UPDATE sobrescribe la fila almacenada con todos los valores de columna de la fila entrante.

COPY NATION FROM stdin DO ON CONFLICT DO UPDATE;

Introduzca la misma fila (separada por tabulaciones):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

No se devuelve ningún error. La fila con N_NATIONKEY = 0 se actualiza para reflejar los nuevos valores:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)

Paso 5: Omitir una fila en conflicto

Utilice DO ON CONFLICT DO NOTHING para ignorar de forma silenciosa la fila entrante cuando ocurra un conflicto:

COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;

Introduzca una fila con N_NATIONKEY = 1, lo cual genera un conflicto con la fila existente de ARGENTINA (separada por tabulaciones):

1	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

No se devuelve ningún error. La fila con N_NATIONKEY = 1 permanece sin cambios:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)