Comment les CTE de Postgres peuvent-ils aider à insérer des données dans plusieurs tables simultanément ?

Utilisation des CTE Postgres pour l'insertion simultanée de données sur plusieurs tables

L'insertion efficace de données dans plusieurs tables de base de données interconnectées est cruciale pour gérer des relations de données complexes. Les expressions de table communes (CTE) de PostgreSQL, en particulier les CTE de modification de données, offrent une solution robuste.

Considérons un scénario impliquant trois tables : sample, sample1 et sample2, définies comme suit :

<code class="language-sql">CREATE TABLE sample (
   id        bigserial PRIMARY KEY,
   lastname  varchar(20),
   firstname varchar(20)
);

CREATE TABLE sample1(
   user_id    bigserial PRIMARY KEY,
   sample_id  bigint REFERENCES sample,
   adddetails varchar(20)
);

CREATE TABLE sample2(
   id      bigserial PRIMARY KEY,
   user_id bigint REFERENCES sample1,
   value   varchar(10)
);</code>

Le défi réside dans la réutilisation des clés générées à partir d'une insertion pour des insertions ultérieures dans des tables associées. Les CTE modifiant les données résolvent élégamment ce problème :

<code class="language-sql">WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
   RETURNING id AS sample_id
   ),
ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;</code>

Cette approche CTE enchaînée garantit une insertion séquentielle, chaque INSERT dépendant du succès de son prédécesseur. L'utilisation de SELECT au lieu de VALUES garantit que les insertions de tables en aval ne se poursuivent que si le INSERT précédent se termine avec succès.

Alternativement, une approche plus centralisée utilise un seul CTE pour définir les lignes de données :

<code class="language-sql">WITH data(firstname, lastname, adddetails, value) AS (
   VALUES ('fai55', 'shaggk', 'ss', 'ss2')
   ),
ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT DISTINCT firstname, lastname
   FROM data
   RETURNING firstname, lastname, id AS sample_id
   ),
ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT ins1.sample_id, d.adddetails
   FROM data d
   JOIN ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT ins2.user_id, d.value
FROM data d
JOIN ins1 USING (firstname, lastname)
JOIN ins2 USING (sample_id);</code>

Cette méthode nécessite une gestion minutieuse des (firstname, lastname) combinaisons en double. Les considérations d'écriture simultanées sont également importantes et nécessitent une enquête plus approfondie (voir les ressources supplémentaires pour plus de détails).

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!