PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Référence » Commandes SQL » INSERT

INSERT

INSERT — Insérer de nouvelles lignes dans une table

Synopsis

[ WITH [ RECURSIVE ] requête_with [, ...] ]
INSERT INTO nom_table [ AS alias ] [ ( nom_colonne [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | requête }
    [ ON CONFLICT [ cible_conflit ] action_conflit ]
    [ RETURNING * | expression_sortie [ [ AS ] nom_sortie ] [, ...] ]

cible_conflit peut valoir :

    ( { nom_colonne_index | ( expression_index ) } [ COLLATE collation ] [ classe_operateur ] [, ...] ) [ WHERE predicat_index ]
    ON CONSTRAINT nom_contrainte

et action_conflit peut valoir :

    DO NOTHING
    DO UPDATE SET { nom_colonne = { expression | DEFAULT } |
                    ( nom_colonne [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( nom_colonne [, ...] ) = ( sous-SELECT )
                  } [, ...]
              [ WHERE condition ]
  

Description

INSERT insère de nouvelles lignes dans une table. Vous pouvez insérer une ou plusieurs lignes spécifiées par les expressions de valeur, ou zéro ou plusieurs lignes provenant d'une requête.

L'ordre des noms des colonnes n'a pas d'importance. Si aucune liste de noms de colonnes n'est donnée, toutes les colonnes de la table sont utilisée dans l'ordre de leur déclaration (les N premiers noms de colonnes si seules N valeurs de colonnes sont fournies dans la clause VALUES ou dans la requête). Les valeurs fournies par la clause VALUES ou par la requête sont associées à la liste explicite ou implicite des colonnes de gauche à droite.

Chaque colonne absente de la liste, implicite ou explicite, des colonnes se voit attribuer sa valeur par défaut, s'il y en a une, ou NULL dans le cas contraire.

Un transtypage automatique est entrepris lorsque l'expression d'une colonne ne correspond pas au type de donnée déclaré.

Des INSERT dans des tables pour lesquelles il manque des index d'unicité ne seront pas bloqués par des activités concurrentes. Les tables avec des index d'unicité pourraient bloquer si des sessions concurrentes réalisent des actions qui verrouillent ou modifient des lignes correspondant aux valeurs en cours d'insertion dans l'index ; les détails sont disponibles dans Section 61.5. ON CONFLICT peut être utilisé pour indiquer une action alternative lorsqu'une erreur sur une contrainte unique ou une contrainte d'exclusion est levée (voir Clause ON CONFLICT ci-dessous).

La clause RETURNING optionnelle fait que INSERT calcule et renvoie le(s) valeur(s) basée(s) sur chaque ligne en cours d'insertion (ou mises à jour si une clause ON CONFLICT DO UPDATE a été utilisée). C'est principalement utile pour obtenir les valeurs qui ont été fournies par défaut, comme un numéro de séquence. Néanmoins, toute expression utilisant les colonnes de la table est autorisée. La syntaxe de la liste RETURNING est identique à celle de la commande SELECT. Seules les lignes qui ont été insérées ou mises à jour avec succès sont retournées. Par exemple, si une ligne a été verrouillée mais non mise à jour parce que la condition de la clause ON CONFLICT DO UPDATE ... WHERE n'a pas été satisfaite, la ligne ne sera pas renvoyée.

Vous devez avoir le droit INSERT sur une table pour insérer des données dedans. Si ON CONFLICT DO UPDATE est indiqué, le droit UPDATE est aussi requis.

Si une liste de colonnes est indiquée, vous avez seulement besoin d'avoir le droit INSERT sur les colonnes spécifiées. De la même manière, lorsque ON CONFLICT DO UPDATE est indiqué, vous avez seulement besoin d'avoir le droit UPDATE sur les colonnes qui sont listées comme à mettre à jour. Cependant, ON CONFLICT DO UPDATE exige également le droit SELECT sur toutes les colonnes dont les valeurs sont lues dans l'expression de ON CONFLICT DO UPDATE ou la condition.

L'utilisation de la clause RETURNING requiert le droit SELECT sur toutes les colonnes mentionnées dans RETURNING. Si vous utilisez la clause requête pour insérer des lignes à partir d'une requête, vous avez bien sûr besoin d'avoir le droit SELECT sur toutes les tables ou colonnes référencées dans la requête.

Paramètres

Insertion

Cette section concerne les paramètres qui peuvent être utilisés lors de l'insertion de nouvelles lignes. Les paramètres exclusivement utilisés avec la clause ON CONFLICT sont décrits séparément.

requête_with

La clause WITH vous permet de spécifier une ou plusieurs sous-requêtes qui peuvent être référencées par leur nom dans la commande INSERT. Voir Section 7.8 et SELECT pour les détails.

Il est possible que la requête (commande SELECT) contienne également une clause WITH. Dans un tel cas, les deux ensembles de requête_with peuvent être référencés à l'intérieur de requête, mais le second prime dans la mesure où il est plus proche.

nom_table

Le nom (éventuellement préfixé du schéma) d'une table existante.

alias

Un nom de substitution pour nom_table. Lorsqu'un alias est indiqué, il masque complètement le nom actuel de la table. Ceci est particulièrement utile lorsque ON CONFLICT DO UPDATE fait référence à une table nommée excluded, puisque sinon ce nom serait utilisé pour le nom de la table spéciale représentant la ligne proposée à l'insertion.

nom_colonne

Le nom d'une colonne dans la table nommée par nom_table. Le nom de la colonne peut être qualifié avec un nom de sous-champ ou un indice de tableau, si besoin. (L'insertion uniquement dans certains champs d'une colonne composite positionne les autres champs à NULL.) Lorsque vous référencez une colonne avec ON CONFLICT DO UPDATE, n'incluez pas le nom de la table dans la spécification de la colonne. Par exemple, INSERT INTO nom_table ... ON CONFLICT DO UPDATE tab SET nom_table.col = 1 est invalide (ceci est conforme au comportement général pour la commande UPDATE).

OVERRIDING SYSTEM VALUE

Sans cette clause, spécifier une valeur explicite (autre que (DEFAULT) pour une colonne d'identité définie comme GENERATED ALWAYS retourne une erreur. Cette clause passe outre cette restriction. restriction.

OVERRIDING USER VALUE

Si cette clause est spécifiée, alors toute valeur fournir pour les colonnes d'identité définies comme GENERATED BY DEFAULT sont ignorées et les valeurs par défaut générée par la séquence sont appliquées.

Cette clause est utile par exemple lors de la copie de valeur entre des tables. Écrire INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 copiera de tbl1 toutes les colonnes de tbl2 qui ne sont pas des colonnes d'identité dans tbl2 alors que des valeurs pour les colonnes d'identité dans tbl2 seront générées par les séquences associées avec tbl2.

DEFAULT VALUES

Toutes les colonnes seront remplies avec leur valeur par défaut. (Une clause OVERRIDING n'est pas permise dans cette forme.)

expression

Une expression ou valeur à assigner à la colonne correspondante.

DEFAULT

La colonne correspondante sera remplie avec sa valeur par défaut.

requête

Une requête (commande SELECT) qui fournit les lignes à insérer. Référez-vous à la commande SELECT pour une description de la syntaxe.

expression_sortie

Une expression à calculer et à retourner par la commande INSERT après que chaque ligne soit insérée ou mise à jour. L'expression peut utiliser n'importe quel nom de colonnes de la table nommée nom_table. Écrivez * pour renvoyer toutes les colonnes de(s) ligne(s) insérée(s) ou mise(s) à jour.

nom_sortie

Un nom à utiliser pour une colonne renvoyée.

Clause ON CONFLICT

La clause optionelle ON CONFLICT indique une action alternative lors d'une erreur de violation d'une contrainte unique ou d'exclusion. Pour chaque ligne individuelle proposée pour l'insertion, soit l'insertion est effectuée, soit si une contrainte arbitrale ou un index indiqué par cible_conflit est violé, l'action alternative cible_conflit est effectuée. ON CONFLICT DO NOTHING évite simplement d'insérer une ligne comme action alternative. Comme action alternative, ON CONFLICT DO UPDATE met à jour la ligne existante en conflit avec la ligne proposée pour l'insertion.

cible_conflit peut effectuer une inférence d'un index unique. L'inférence consiste à indiquer un ou plusieurs nom_colonne_index et/ou expression_index. Tous les index uniques de nom_table qui, indépendamment de l'ordre, contiennent exactement les colonnes/expressions cible_conflit spécifiées sont inférés (choisis) comme index arbitraux. Si un predicat_index est indiqué, il doit, comme une condition supplémentaire pour l'inférence, satisfaire les index arbitraux. Notez que cela signifie qu'un index unique non partiel (un index unique sans prédicat) sera inféré (et donc utilisé par ON CONFLICT) si un tel index remplissant l'ensemble des autres critères est disponible. Si une tentative d'inférence est impossible, une erreur est levée.

ON CONFLICT DO UPDATE garantit un traitement atomique de INSERT ou de UPDATE ; dans la mesure où il n'y a pas d'erreur indépendante, l'un de ces deux traitements est garanti, y compris en cas d'accès concurrents. Ceci est aussi connu sous le nom d'UPSERT (« UPDATE ou INSERT »).

cible_conflit

Indique les conflits ON CONFLICT entrainant l'action alternative en choisissant les index arbitraux. Soit effectue l'inférence d'un index unique, soit nomme une contrainte explicitement. Pour ON CONFLICT DO NOTHING, l'indication de cible_conflit est facultatif ; s'il est omis, les conflits avec toutes les contraintes utilisables (et index uniques) sont retenus. Pour ON CONFLICT DO UPDATE, cible_conflit doit être indiqué.

action_conflit

action_conflit indique une action alternative à ON CONFLICT. Elle peut être soit une clause DO NOTHING, soit une clause DO UPDATE indiquant le détail exact de l'action UPDATE à effectuer en cas de conflit. Les clauses SET et UPDATE dans ON CONFLICT DO UPDATE ont accès à la ligne existante en utilisant le nom de la table (ou un alias), et à la ligne proposée à l'insertion en utilisant la table spéciale de nom excluded. Le droit SELECT est requis sur l'ensemble des colonnes de la table cible où les colonnes correspondantes de excluded sont lues.

Notez que les effets de tous les trigegrs par ligne BEFORE INSERT sont reflétés dans les valeurs de excluded, dans la mesure où ces effets peuvent avoir contribués à la ligne exclue de l'insertion.

nom_colonne_index

Le nom d'une colonne de nom_table. Utilisé pour inférer les index arbitraux. Suit le format de CREATE INDEX. Le droit SELECT sur nom_colonne_index est nécessaire.

expression_index

Similaire à nom_colonne_index, mais utilisé pour inférer les expressions sur les colonnes de nom_table apparaissant dans les définitions de l'index (pas de simples colonnes). Suit le format de CREATE INDEX. Le droit SELECT sur toutes les colonnes apparaissant dans expression_index est nécessaire.

collation

Lorsque mentionné, indique que la colonne nom_colonne_index correspondante ou expression_index utilise une collation particulière pour être mis en correspondance durant l'inférence. Typiquement, ceci est omis, dans la mesure où les collations n'ont généralement pas d'incidence sur la survenu ou non d'une violation de contrainte. Suit le format de CREATE INDEX.

classe_operateur

Lorsque mentionné, elle indique que la colonne nom_colonne_index correspondante ou expression_index utilise une classe d'opérateur en particulier pour être mis en correspondance durant l'inférence. Typiquement, ceci est omis, dans la mesure où les sémantiques d'égalité sont souvent équivalentes entre les différents types de classes d'opérateurs, ou parce qu'il est suffisant de s'appuyer sur le fait que les définitions d'index uniques ont une définition pertinente de l'égalité. Suit le format de CREATE INDEX.

predicat_index

Utilisé pour permettre l'inférence d'index uniques partiels. Tous les index qui satisfont le prédicat (qui ne sont pas nécessairement des index partiels) peuvent être inférés. Suit le format de CREATE INDEX. Le droit SELECT sur toutes les colonnes apparaissant dans predicat_index est nécessaire.

nom_contrainte

Spécifie explicitement une contrainte arbitrale par nom, plutôt que d'inférer une contrainte par nom ou index.

condition

Une expression qui renvoie une valeur de type boolean. Seules les lignes pour lesquelles cette expression renvoie true seront mises à jour, bien que toutes les lignes seront verrouillées lorsque l'action ON CONFLICT DO UPDATE est prise. Notez que condition est évaluée en dernier, après qu'un conflit ait été identifié comme un candidat à la mise à jour.

Notez que les contraintes d'exclusion ne sont pas supportées comme arbitres avec ON CONFLICT DO UPDATE. Dans tous les cas, seules les contraintes NOT DEFERRABLE et les index uniques sont supportés comme arbitres.

La commande INSERT avec une clause ON CONFLICT DO UPDATE est une instruction déterministe. Ceci signifie que la commande ne sera pas autorisée à modifier n'importe quelle ligne individuelle plus d'une fois ; une erreur de violation de cardinalité sera levée si cette situation arrive. Les lignes proposées à l'insertion ne devraient pas avoir de duplication les unes par rapport aux autres relativement aux attributs contraints par un index arbitral ou une contrainte.

Notez qu'il n'y a pas de support d'une clause ON CONFLICT DO UPDATE d'un INSERT appliquée à une table partitionnée pour mettre à jour la clé de partitionnement d'une ligne en conflit qui causerait le déplacement de la ligne dans une nouvelle partition.

Astuce

Il est souvent préférable d'utiliser l'inférence d'un index unique plutôt que de nommer une contrainte directement en utilisant ON CONFLICT ON CONSTRAINT nom_contrainte. L'inférence continuera de fonctionner correctement lorsque l'index sous-jacent est remplacé par un autre plus ou moins équivalent de manière recouvrante, par exemple en utilisant CREATE UNIQUE INDEX ... CONCURRENTLY avant de supprimer l'index remplacé.

Sorties

En cas de succès, la commande INSERT renvoie un code de la forme

INSERT oid nombre 

nombre correspond au nombre de lignes insérées ou mises à jour. oid vaut toujours 0 (il s'agissait de l'OID affecté à la ligne insérée si count valait exactement 1 et que la table cible était déclarée WITH OIDS et 0 dans les autres cas, mais créer une table WITH OIDS n'est plus supporté).

Si la commande INSERT contient une clause RETURNING, le résultat sera similaire à celui d'une instruction SELECT contenant les colonnes et les valeurs définies dans la liste RETURNING, à partir de la liste des lignes insérées ou mises à jour par la commande.

Notes

Si la table spécifiée est une table partitionnée, chaque ligne est redirigée vers la partition appropriée et insérée dedans. Si la table spécifiée est une partition, une erreur sera remontée si une des lignes en entrée viole la contrainte de partition.

Exemples

Insérer une ligne dans la table films :

INSERT INTO films
	VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comédie', '82 minutes');
   

Dans l'exemple suivant, la colonne longueur est omise et prend donc sa valeur par défaut :

INSERT INTO films (code, titre, did, date_prod, genre)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drame');
   

L'exemple suivant utilise la clause DEFAULT pour les colonnes date plutôt qu'une valeur précise :

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comédie', '82 minutes');
INSERT INTO films (code, titre, did, date_prod, genre)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drame');
   

Insérer une ligne constituée uniquement de valeurs par défaut :

INSERT INTO films DEFAULT VALUES;
   

Pour insérer plusieurs lignes en utilisant la syntaxe multi-lignes VALUES :

INSERT INTO films (code, titre, did, date_prod, genre) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
   

Insérer dans la table films des lignes extraites de la table tmp_films (la disposition des colonnes est identique dans les deux tables) :

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
   

Insérer dans des colonnes de type tableau :

-- Créer un jeu de 3 cases sur 3
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- Les indices de l'exemple ci-dessus ne sont pas vraiment nécessaires
INSERT INTO tictactoe (game, board)
    VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
   

Insérer une ligne simple dans la table distributeurs, en renvoyant le numéro de séquence généré par la clause DEFAULT :

INSERT INTO distributeurs (did, dnom) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;
   

Augmenter le nombre de ventes du vendeur qui gère le compte Acme Corporation, et enregistrer la ligne complètement mise à jour avec l'heure courante dans une table de traçage :

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
   

Insérer ou mettre à jour de nouveaux distributeurs comme approprié. Suppose qu'un index unique a été défini qui contraint les valeurs apparaissant dans la colonne did. Notez que la table spéciale excluded est utilisée pour référencer les valeurs proposées à l'origine pour l'insertion :

INSERT INTO distributeurs (did, dnom)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dnom = EXCLUDED.dnom;
   

Insérer un distributeur, ou ne fait rien pour les lignes proposées à l'insertion lorsqu'une ligne existante, exclue (une ligne avec une contrainte correspondante sur une ou plusieurs colonnes après que les triggers après ou avant se soient déclenchés) existe. L'exemple suppose qu'un index unique a été défini qui contraint les valeurs apparaissant dans la colonne did :

INSERT INTO distributeurs (did, dnom) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;
   

Insérer ou mettre à jour de nouveaux distributeurs comme approprié. L'exemple suppose qu'un index unique a été défini qui contraint les valeurs apparaissant dans la colonne did. La clause WHERE est utilisée pour limiter les lignes mises à jour (toutes les lignes existantes non mises à jour seront tout de même verrouillées) :

-- Ne pas mettre à jour les distributeurs existants avec un certain code postal
INSERT INTO distributeurs AS d (did, dnom) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dnom = EXCLUDED.dnom || ' (précédemment ' || d.dnom || ')'
    WHERE d.code_postal <> '21201';

-- Nomme une contrainte directement dans l'instruction (utilise
-- l'index associé pour décider de prendre l'action DO NOTHING)
INSERT INTO distributeurs (did, dnom) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributeurs_pkey DO NOTHING;
   

Insérer un nouveau distributeur si possible ; sinon DO NOTHING. L'exemple suppose qu'un index unique a été défini qui contraint les valeurs apparaissant dans la colonne did à un sous-ensemble des lignes où la colonne booléenne est_actif est évaluée à true :

-- Cette instruction pourrait inférer un index unique partiel sur "did"
-- avec un prédicat de type "WHERE est_actif", mais il pourrait aussi
-- juste utiliser une contrainte unique régulière sur "did"
INSERT INTO distributeurs (did, dnom) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE est_actif DO NOTHING;
   

Compatibilité

INSERT est conforme au standard SQL, sauf la clause RETURNING qui est une extension PostgreSQL, comme la possibilité d'utiliser la clause WITH avec l'instruction INSERT, et de spécifier une action alternative avec ON CONFLICT. Le standard n'autorise toutefois pas l'omission de la liste des noms de colonnes alors qu'une valeur n'est pas affectée à chaque colonne, que ce soit à l'aide de la clause VALUES ou à partir de la requête.

The SQL standard spécifie que OVERRIDING SYSTEM VALUE ne peut être spécifié que si une colonne d'identité qui est toujours générée existe. PostgreSQL autorise cette clause dans tous les cas et l'ignore si elle ne s'applique pas.

Les limitations possibles de la clause requête sont documentées sous SELECT.