«Upsert» des lignes d'une table depuis une autre table

Bonjour à tous,

je suis dans une situation ou j’ai une table «DESTINATION» contenant N colonnes avec un identifiant ID. J’ai une autre table «SOURCE», ayant le même schéma que DESTINATION le le même ID.

Je dois mettre à jour toutes les colonnes des lignes de DESTINATION qui depuis SOURCE ou ID est égal, et ajouter les lignes DANS DESTINATION qui ne sont pas présentes dans SOURCE.

Un exemple.

La table SOURCE:

col_a  | col_b | ID
——————————————————
a      | a     | 1
b      | b     | 2
c      | c     | 4

La table DESTINATION:

col_a | col_b | ID
——————————————————
z     | z     | 1
x     | x     | 2
y     | y     | 3

Le résultat attendu dans DESTINATION:

col_a | col_b | ID
——————————————————
a      | a    | 1
b      | b    | 2
c      | c    | 4
y      | y    | 3

Mon implémentation naïve:

• Supprimer les lignes de la table DESTINATION contenues dans la table SOURCE:

DELETE source_table 
FROM SOURCE source_table
JOIN DESTINATION destination_table ON source_table.ID = destination_table.ID;

• Insérer les lignes de SOURCE dans DESTINATION:

INSERT INTO DESTINATION
SELECT * FROM SOURCE ;

Cela marche mais je suis convaincu qu’il y a plus optimisé alors si vous partagez mon avis et que vous savez comment faire: vous combleriez ma curiosité avec une réponse .

Attention , il y a une contrainte: la/les requête doivent être agnostiques du nom des colonnes autres que «ID» (je dois appliquer ces transformations à plusieurs tables ayant des schémas différents mais ayant toutes une colonne ID de même type).

Pour être honnête c’est cette contrainte qui m’empêche de former une requête plus optimisée à base d’UPDATE puis INSERT. D’ailleurs mais les infos que j’ai trouvé en ce sens me laissent penser que ma question n’a peut être pas de réponses plus optimisées que ma réponse «naïve».

Hello,

Est-ce que tu as regardé du côté de la syntaxe insert ... select ou replace ... select et ses variantes ? IL doit sûrement y avoir ce que tu veux en ne touchant pas les lignes qui n’ont pas été modifiées.

Syntaxe générale: insert into destination (colonnes_destination) select colonnes_source from source where .... Il faut juste faire très attention à l’ordre des éléments dans le select qui doivent correspondre à l’ordre des colonnes indiquées dans l’insert/replace.

Ensuite pour supprimer ce qui ne se trouve plus dans la source, tu dois pouvoir faire une requête du genre
delete from destination where not exists (select source.id from source where source.id = destination.id)
IL me semble que certains SGBDR demandent de passer par une table temporaire car on pose des conditions sur la table dans laquelle on veut supprimer, mais ce n’est pas un gros problème à contourner.

Merci pour vos réponses

Elles sont toutes deux intéressantes mais dans les deux cas, je ne trouve pas de moyens de les utiliser en restant dans le cadre la contrainte:

(j’ajoute qu’il s’agit bien sûr de «paires» de tables ayant bien sûr les mêmes schémas)

Qu’en pensez vous ?

Salut

Pourrais-tu préciser ton cas d’usage (donc pas en langage technique) pour avoir besoin d’un tel processus ?

D’une part, suivant le cas, je pense que beaucoup s’en sortiraient avec une date de mise à jour et une colonne spécifique, et d’autre part, peut-être qu’une view SQL (en plus ?) pourrait aussi être une solution.

Bien sûr,

Chaque jour je reçois plusieurs DataFrames de quelques centaines de Mo (je précise car cela me semble important) et de structure différentes, mais dont chacun a une colonne qui identifie chaque lignes.

Chacun de ces DataFrames a une table dans la base Postgres, de structure qui lui correspond et qui contient l’état actuel correspondant à l’upsert (ajout ou mise à jour des lignes existantes grâce à l’ID) des DataFrames des jours précédents.

L’objectif est d’upsert cette table grâce au DataFrames du jour, de préférence de manière optimisée.

Heureusement que j’avais précisé « pas en langage technique »…

En gros, tu as des données qui rentrent pour des [états de je-ne-sais-quoi], et tu souhaites avoir les derniers [états] ?

Y a-t’il une contrainte qui nous échappe pour devoir utiliser une table d’entrée et une table "à jour" ?

Non, il n’y a pas de contraintes particulières, cette table intermédiaire est juste la solution la moins mauvaise que j’ai trouvé pour le momment.

Il me semble que l’exemple de mon premier message décrit le problème de manière assez simple (je le reproduis avec quelques annotations en plus):

Les données en entrée:

col_a  | col_b | ID
——————————————————
a      | a     | 1
b      | b     | 2
c      | c     | 4

La table Postgres à mettre à jour:

col_a | col_b | ID
——————————————————
z     | z     | 1
x     | x     | 2
y     | y     | 3

Le résultat attendu dans la table à mettre à jour:

col_a | col_b | ID
——————————————————
a      | a    | 1 -> col_a et col_b mises à jour depuis les données d'entrées
b      | b    | 2 -> col_a et col_b mises à jour depuis les données d'entrées
c      | c    | 4 -> Ajoutée depuis les données d'entrées
y      | y    | 3 -> Inchangée, non préseente dans les données d'entrées.

Ce même traitement dois être appliqués sur plusieurs tables ayant toutes une colonne ID et des schémas simples mais différents. J’utilise pour cela une commade Python qui charge les DataFrames associés à une liste de tables correspondantes.

Le hic, c’est que tu restes dans l’implémentation technique avec ces descriptions, ce qui ne m’aide pas vraiment à comprendre le cas d’usage comme on l’expliquerait à un client qui n’y connait rien à la technique. Or, cet exercice pourrait aider à proposer une alternative qui pourrait être différente sinon meilleure.

Je maintiens cependant ce que j’ai déjà proposé précédemment : une colonne qui contient la date d’insertion (qui peut être "automatisée") et une requête qui prend les derniers enregistrements par valeur de la colonne ID, quitte à faire une view avec cette requête, ce qui permettrait de se passer de l’étape de duplication d’une table à l’autre qui me paraît étrange. La requête serait du genre de celles proposées dans cette réponse sur StackOverflow.

Et si vraiment tu veux garder une table pour y copier les données, du moment que tu arrives à récupérer les dernières mises à jour, avec une jointure bien définie, tu peux avoir ce qu’il faut pour remplacer et insérer dans la table cible.

Edit

D’autre part, si tu n’as pas besoin de conserver d’historique des entrées, alors tu devrais simplement pouvoir utiliser la commande MERGE comme mentionné précédemment. Sachant que tu dois de toute manière définir les tables source et cible qui, d’après ce que tu nous dis, ont des structures sinon identiques au moins comparables, alors cela devrait être indépendant des colonnes.

Reste une question : cette colonne ID, c’est l’ID primaire de la table ou c’est plus à considérer comme une clé étrangère ?

Je retente donc de décrire le cas d’usage, mais dans la mesure où mon client est un logiciel, j’aurais du mal à me passer de technique :).

Un logiciel de visualisation de données vient collecter ses métriques dans une BDD, celle qui nous intéresse. Cette BDD est alimentée en métriques à propos de divers types objets (par ex. ventes, points de ventes, clients…), un type d’objet par table.

Chacun de ses objets est identifié par une clé primaire unique (appelée ID).

Chaque jour, des CSVs sont reçu, par le programme afin d’ajouter/MaJ ces objets, dans la BDD, le programme les traites puis fait l’ajout / MaJ dans les différentes tables leur correspondant en se basant sur leurs IDs.

Auparavant, ma solution était, pour chaque table, de sortir pour chaque table depuis le CSV, la colonnes ID des CSV correspondant, puis de faire dans la BDD un suppression grâce à un WHERE IN (<la liste d’ID>) et enfin une insertion de l’ensemble des données issues du CSV dans la table (ça marche, mais cela ne me semble pas optimal).

Ainsi, j’ai changé mon implém pour l’utilisation d’une table intermédiaire qui permet d’écrire d’abord «toute les données issues du CSV» faire les traitements avec Postgres.

En effet, MERGE me semble être la solution la plus adéquate, même si contrairement à ma solution naïve, il me faudra faire un peu d’introspection pour récupérer les noms de chaque colonnes de chaque tables (pour la clause UPDATE).

Je ne comprend pas quelle information ajoute la date d’insertion qui pourrait permettre de se passer de la table intermédiaire

C’est un ID primaire.

Bon, on va laisser tomber le besoin. J’en ai une idée, peut-être pas totalement précise, mais à mon avis suffisante.

Effectivement, je n’avais pas fait attention à l’implémentation nécessaire pour la clause UPDATE. Mon raisonnement se basait principalement sur MySQL qui malheureusement utilise des solutions qui ne sont pas dans le standard ANSI, dont est issu MERGE implémentée par PostgreSQL.

Une date d’insertion permettrait de savoir si l’enregistrement dans la table source est plus récent que l’enregistrement de la table cible (si d’aventure il fallait conserver un historique), et donc d’avoir l’information si on doit la mettre à jour ou non. Et avec une jointure bien faite, on peut récupérer tout ce qui est plus récent dans la table source ainsi que ce qui n’est pas dans la cible.

Mais surtout une colonne de date d’insertion pourrait permettre de faire les statistiques sans avoir à effectuer de copie des données et en les conservant toutes telles que récupérées, à mon sens. Les exemples de requête dans ce que j’ai fourni précédemment permettent de récupérer les valeurs les plus récentes, et avec une clause WHERE bien définie, on peut spécifier une période à considérer.

En bref, mon avis est qu’il n’y a pas nécessairement besoin de tables source et cible, cette dernière étant apparemment pour persister un état à un instant donné — et dans le cas de rapports, je pense qu’on préférerait une version mise en page aux données brutes.

Ainsi, MERGE sera utilisé en lieu et place de ma solution actuelle à base de:

DELETE FROM destination_table USING source_table WHERE destination_table.ID = source_table.ID;
WITH deleted_rows as (
        DELETE FROM source_table
        RETURNING *
        ) 
        INSERT INTO destination_table SELECT deleted_rows.* FROM deleted_rows;

puisque en effet, le problème des colonnes n’est pas insurmontable.

@Ymox , j’aurais sans doute dû te l’expliquer après ta première réponse, mais je ne souhaite pas obtenir une solution plus adaptée que l’actuelle à un besoin métier. Ma question initiale découle simplement de la curiosité de savoir si il existait une méthode plus optimisée pour répondre au cas décrit.

En tous cas, je te remmercie pour ton temps ansi qu’a vous autres pour vos réponses .