Comment faire pour insérer une instruction PostgreSQL Bulk INSER sans violation de clé primaire

npgsql postgresql sqlbulkcopy

Question

J'essaie d'obtenir une abstraction de la base de données dans mon projet, mais maintenant, je me suis retrouvé avec un INSERT en bloc dans PostgreSQL. Mon projet est en C # et j'utilise PostgreSQL 9.3 avec npgsql.dll 2.0.14.

Pour Microsoft SQL Server, j'effectue un INSERT en bloc en concaténant toutes les instructions, puis en exécutant un ExecuteNonQuery:

IF NOT EXISTS (SELECT id FROM table WHERE id = 1) INSERT INTO table (id) VALUES (1);
IF NOT EXISTS (SELECT id FROM table WHERE id = 2) INSERT INTO table (id) VALUES (2);
IF NOT EXISTS (SELECT id FROM table WHERE id = 3) INSERT INTO table (id) VALUES (3);

Bien que la clause IF-NOT-EXISTS puisse être substituée dans PostgreSQL par un SELECT-WHERE, cette approche ne fonctionne malheureusement toujours pas car chaque instruction dans PostgreSQL est validée séparément.

J'ai donc cherché sur Google une autre solution et trouvé l'approche consistant à utiliser la commande COPY avec NpgsqlCopySerializer / NpgsqlCopyIn pour "diffuser" de manière performante les données en bloc. Mais maintenant, je reçois constamment des erreurs de violation de clé primaire - parce que la clause EXISTS / WHERE ne peut apparemment pas être utilisée avec l'instruction COPY .

Je voudrais vraiment éviter de faire les INSERTs un par un, cela ralentirait considérablement mon application, donc j'espère que tout le monde aura déjà résolu ce problème!

Réponse acceptée

En règle générale, dans ce type de situation, je disposerais d'une table intermédiaire séparée ne contenant pas la contrainte PK, que je remplirais à l'aide de COPY (en supposant que les données étaient dans un format pour lequel il est logique de faire une COPY ). Ensuite, je ferais quelque chose comme:

insert into table
select a.*
from staging a
where not exists (select 1
                  from table
                  where a.id = b.id)

Cette approche n’est pas trop éloignée de votre conception originale.

Je ne comprends pas totalement cette partie de votre question, cependant, qui ne semble même pas totalement pertinente pour votre question:

Malheureusement, cette approche ne fonctionne toujours pas car chaque instruction de PostgreSQL est validée séparément.

Ce n'est pas vrai du tout, pas pour les SGBDR. Bien sûr, la validation automatique peut être activée sur votre client, mais cela ne signifie pas que postgres valide chaque déclaration séparément et que vous ne pouvez pas désactiver la validation automatique. Cette approche fonctionnerait:

begin;
insert into table (id) select 1 where not exists (select 1 from table where id = 1);
insert into table (id) select 2 where not exists (select 1 from table where id = 2);
insert into table (id) select 3 where not exists (select 1 from table where id = 3);
commit;

Comme vous l'avez souligné, cependant, si vous avez plus d'une poignée de déclarations de ce type, vous allez rapidement avoir des problèmes de performances.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi