Utiliser COPY ON CONFLICT pour écraser les données - AnalyticDB

Lorsque vous importez des données dans AnalyticDB for PostgreSQL à l'aide de COPY, l'opération échoue si une ligne entrante entre en conflit avec une contrainte de table existante. La commande COPY ON CONFLICT étend l'instruction standard COPY FROM afin de gérer ces conflits sans interrompre l'importation : vous pouvez soit écraser la ligne conflictuelle, soit l'ignorer silencieusement.

La fonctionnalité COPY ON CONFLICT est prise en charge uniquement sur les instances AnalyticDB for PostgreSQL V6.0 exécutant la version mineure du moteur 20210528 ou ultérieure. Mettez à jour votre instance vers la dernière version mineure du moteur avant d'utiliser cette fonctionnalité. Pour obtenir des instructions, consultez la rubrique Mettre à jour la version mineure du moteur .

Prérequis

Avant de commencer, assurez-vous que vous disposez des éléments suivants :

Une instance AnalyticDB for PostgreSQL V6.0 exécutant la version mineure du moteur 20210528 ou ultérieure
Une table heap (table standard) dotée d'une clé primaire ou d'une contrainte d'unicité, servant de cible d'importation

Limites

La table cible doit être une table heap. Les tables optimisées pour l'ajout (AO) ne sont pas prises en charge, car elles ne disposent pas d'index uniques.
Les tables partitionnées sont prises en charge uniquement à partir de la version mineure du moteur 6.3.6.1. Pour connaître la procédure de mise à niveau, consultez la rubrique Mettre à jour la version mineure du moteur.
La table cible ne peut pas être une vue modifiable.
La commande COPY ON CONFLICT prend en charge uniquement le sens COPY FROM . Le sens COPY TO n'est pas pris en charge.
Le paramètre CONFLICT index n'est pas pris en charge. La commande COPY ON CONFLICT vérifie systématiquement toutes les contraintes de la table, et non un index spécifique. Si vous spécifiez un index de conflit, l'opération échoue avec le message suivant :
```
ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
```
Le paramètre UPDATE SET n'est pas pris en charge. L'instruction COPY ON CONFLICT DO UPDATE écrase toujours toutes les colonnes de la ligne conflictuelle, et non un sous-ensemble. Si vous spécifiez UPDATE SET , l'opération échoue avec le message suivant :
```
ERROR:  COPY ON CONFLICT does NOT support UPDATE SET targets
```

Syntaxe

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 clause de résolution des conflits située à la fin détermine le comportement lorsqu'une ligne entrante correspond à une contrainte existante :

Clause	Comportement
`DO ON CONFLICT DO UPDATE`	Écrase la ligne existante avec toutes les colonnes issues des données entrantes.
`DO ON CONFLICT DO NOTHING`	Ignore la ligne entrante. La ligne existante reste inchangée.

Exemple

L'exemple suivant décrit un scénario complet : importation de données, déclenchement d'un conflit de contrainte et résolution de ce dernier à l'aide de chaque clause.

Étape 1 : Créer une table

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

La colonne N_NATIONKEY constitue la clé primaire et garantit l'unicité de toutes les lignes.

Étape 2 : Importer les données initiales

COPY NATION FROM stdin;

Lorsque l'invite >> s'affiche, saisissez les lignes suivantes. Utilisez un caractère de tabulation entre les valeurs de colonne.

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'
\.

Vérifiez l'importation :

SELECT * FROM NATION;

Résultat attendu :

 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)

Étape 3 : Observer une erreur de conflit

Tenter d'insérer une ligne avec N_NATIONKEY = 0 — valeur déjà existante — entraîne un échec avec une instruction COPY standard :

COPY NATION FROM stdin;

Saisissez la ligne suivante (séparée par des tabulations) :

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

L'opération échoue :

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

Étape 4 : Écraser la ligne conflictuelle

Utilisez DO ON CONFLICT DO UPDATE pour remplacer la ligne existante par les données entrantes. En cas de conflit, COPY ON CONFLICT DO UPDATE écrase la ligne stockée en utilisant toutes les valeurs de colonne de la ligne entrante.

COPY NATION FROM stdin DO ON CONFLICT DO UPDATE;

Saisissez la même ligne (séparée par des tabulations) :

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

Aucune erreur n'est renvoyée. La ligne avec N_NATIONKEY = 0 est mise à jour pour refléter les nouvelles valeurs :

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)

Étape 5 : Ignorer une ligne conflictuelle

Utilisez DO ON CONFLICT DO NOTHING pour ignorer silencieusement la ligne entrante en cas de conflit :

COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;

Saisissez une ligne avec N_NATIONKEY = 1 , qui entre en conflit avec la ligne ARGENTINA existante (séparée par des tabulations) :

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

Aucune erreur n'est renvoyée. La ligne avec N_NATIONKEY = 1 reste inchangée :

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)