💡 PostgreSQL : INSERT ou UPDATE ?
Si vous travaillez régulièrement avec des bases de données PostgreSQL, vous vous êtes sans doute posé cette question existentielle : « dois-je faire un INSERT ou un UPDATE ? ».
Moi aussi, je me suis déjà pris la tête avec ce dilemme infernal. Heureusement, PostgreSQL nous offre une porte de sortie parfaite : l’UPSERT (ou INSERT ... ON CONFLICT pour les puristes). Un vrai game-changer pour gérer les conflits de données !
Dans cet article, je vous montre comment réaliser facilement des opérations dynamiques grâce à une fonction PostgreSQL personnalisée, affectueusement nommée z_upsert.
Ah oui, j’ai failli oublier ! Comme toujours, Marius, mon fidèle assistant IA, sera là pour glisser quelques remarques et conseils avisés. Go !
Bonjour !
Je suis Jean-Marc HENRY, ingénieur ESI, consultant IT/IS pour les entreprises depuis plus de 35 ans, et fondateur de LMVI Conseil.
À travers ce blog, je vous propose d’explorer ensemble tous les 15 jours les grands ou petits (!) sujets de l’informatique.
Ici, on parlera de sujets qui me servent quotidiennement et qui me tiennent à cœur, comme le Nocode, l’IA, l’IT, l’IS ou l’architecture logicielle et un peu WSO2.
D’ailleurs, je ne suis pas seul à rédiger ces billets !
Je suis accompagné de mon assistant IA prénommé Marius. C’est un bon pote d’Ollama et de ChatGPT (entre autres, car il a un sacré réseau !).
Il est assez secret et ne me dit pas tout sur la manière dont il m’aide à écrire mes articles. En revanche, je ne publie rien qui n’ait été validé par des sources sûres ou testé !
C’est parti, on vous embarque !
Qu’est-ce que PostgreSQL ?
PostgreSQL est un puissant système de base de données, ultra-fiable, open source et orienté objet capable de prendre en charge en toute sécurité les charges de travail de données les plus complexes : différentes fonctions de SQL, comme les clés étrangères, les sous-requêtes, les déclencheurs, et différents types et fonctions définis par l’utilisateur.
Alors que MySQL donne la priorité à l’évolutivité et aux performances, Postgres donne la priorité à la conformité et à l’extensibilité SQL.
Il augmente aussi le langage SQL grâce à des fonctionnalités de mise à l’échelle et en réservant les charges de travail de données.
Il est généralement utilisé pour stocker les données de nombreuses applications mobiles, web, géospatiales, et analytiques.
PostgresSQL peut être téléchargé gratuitement et déployé sur du matériel standard ou dans le Cloud.
1. Introspection PostgreSQL : votre meilleur alliée.
L’idée centrale de ma solution repose sur l’introspection. Pourquoi ? Parce qu’elle permet à PostgreSQL de détecter automatiquement les types de données de vos colonnes. Résultat : moins d’erreurs, plus de souplesse.
🤖 Marius : « L’introspection PostgreSQL, c’est comme un GPS 🗺️ pour ton code : impossible de se perdre dans le schéma ! »
Grâce à cette approche, je n’ai plus à m’inquiéter des évolutions de mes tables ! Si j’ajoute ou supprime une colonne, ma fonction s’adapte toute seule, sans rien casser..
Magique ? Non. Juste malin. 💡
2. Création d’une table générique z_test_upsert.
Avant d’aller plus loin, voici comment créer une table polyvalente intégrant différents types de données. L’objectif ? Illustrer pleinement les capacités de notre fonction et s’assurer qu’elle s’adapte à toutes les situations. 🚀
CREATE TABLE z_test_upsert (
id SERIAL PRIMARY KEY,
nom VARCHAR(100),
age INTEGER,
email VARCHAR(100),
actif BOOLEAN DEFAULT TRUE,
score NUMERIC(10,2),
date_inscription DATE,
derniere_connexion TIMESTAMP,
preferences JSONB
);
🤖 Marius : « Avec cette commande, on demande gentiment à Kafka : « Dis-moi où en est le dernier message ! » 📢«
3. Mise en pratique : z_upsert, la fonction générique ultime.
z_upsert, une fonction PostgreSQL puissante et flexible pour gérer vos INSERT ... ON CONFLICT. ⬇️
CREATE OR REPLACE FUNCTION z_upsert(
p_table_name TEXT,
p_primary_key TEXT,
p_data JSONB
)
RETURNS JSONB AS $$
DECLARE
sql TEXT;
cols TEXT := '';
vals TEXT := '';
upd TEXT := '';
result JSONB;
col RECORD;
first BOOLEAN := TRUE;
BEGIN
FOR col IN
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table_name
AND column_name IN (SELECT jsonb_object_keys(p_data))
LOOP
IF NOT first THEN
cols := cols || ', ';
vals := vals || ', ';
END IF;
cols := cols || quote_ident(col.column_name);
vals := vals || CASE
WHEN col.data_type IN ('integer', 'bigint', 'smallint') THEN
format('%s', (p_data ->> col.column_name)::bigint)
WHEN col.data_type IN ('numeric', 'real', 'double precision') THEN
format('%s', (p_data ->> col.column_name)::numeric)
WHEN col.data_type = 'boolean' THEN
format('%L::boolean', (p_data ->> col.column_name))
WHEN col.data_type IN ('json', 'jsonb') THEN
format('%L::jsonb', (p_data -> col.column_name)::text)
ELSE
format('%L', (p_data ->> col.column_name))
END;
IF col.column_name <> p_primary_key THEN
upd := upd || CASE WHEN upd <> '' THEN ', ' ELSE '' END
|| format('%1$I = EXCLUDED.%1$I', col.column_name);
END IF;
first := FALSE;
END LOOP;
sql := format(
'INSERT INTO %I (%s) VALUES (%s) ON CONFLICT (%I) DO UPDATE SET %s RETURNING row_to_json(%I.*);',
p_table_name, cols, vals, p_primary_key, upd, p_table_name
);
EXECUTE sql INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
4. Exemples concrets avec z_test_upsert
Voici un exemple concret de l’utilisation de notre fonction, intégrant différents types de données :
SELECT z_upsert(
'z_test_upsert',
'id',
'{"id":1, "nom":"Jean", "age":30, "email":"jean@exemple.com", "actif":true, "score":95.50, "date_inscription":"2023-01-15", "derniere_connexion":"2023-05-01 10:30:00", "preferences":{"langue":"fr","notifications":true}}'
);
5. Attention aux pièges
Bien que cette fonction soit ultra pratique, il est crucial de toujours valider et sécuriser les entrées utilisateur.
Cela permet d’éviter les mauvaises surprises comme l’injection SQL, les types de données incorrects, et bien d’autres risques. 🛡️
🤖 Marius : « Rappelle-toi : avec de grands pouvoirs SQL viennent de grandes responsabilités… de validation ! » 😅
🎯Conclusion : moins de stress, plus d’efficacité.
Avec z_upsert, vous allez non seulement gagner en clarté, efficacité et sécurité dans vos projets PostgreSQL, mais aussi simplifier considérablement les processus d’insertion/mise à jour. Tout en gardant une flexibilité et une générosité de fonctionnement. 🚀
Et comme dirait Marius en guise de mot de la fin : « Si tu veux éviter les conflits, que ce soit en SQL ou lors des réunions, adopte vite l’UPSERT générique ! » 😄
Qui sommes-nous ?
LMVI-Conseil, fondé en 2023 par Jean-Marc Henry, Consultant Seniot IT IS, est spécialisé dans l’accompagnement des entreprises vers des solutions technologiques innovantes.
Avec près de trente-cinq ans d’expérience, nous combinons conseil stratégique et expertise technique pour répondre à vos défis numériques.