INSERT — Insérer de nouvelles lignes dans une table
[ WITH [ RECURSIVE ]requête_with
[, ...] ] INSERT INTOnom_table
[ ASalias
] [ (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
] } [, ...] ] oùcible_conflit
peut valoir : ( {nom_colonne_index
| (expression_index
) } [ COLLATEcollation
] [classe_operateur
] [, ...] ) [ WHEREpredicat_index
] ON CONSTRAINTnom_contrainte
etaction_conflit
peut valoir : DO NOTHING DO UPDATE SET {nom_colonne
= {expression
| DEFAULT } | (nom_colonne
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (nom_colonne
[, ...] ) = (sous-SELECT
) } [, ...] [ WHEREcondition
]
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 64.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.
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
Si cette clause est indiquée, alors toutes les valeurs fournies par les colonnes d'identité surchargeront les valeurs par défaut générées par les séquences.
Pour une colonne d'identité définie comme GENERATED
ALWAYS
, insérer une valeur explicite (autre que
DEFAULT
) sans préciser soit OVERRIDING
SYSTEM VALUE
soit OVERRIDING USER VALUE
est considéré comme une erreur. (Pour une colonne d'identité définie
comme GENERATED BY DEFAULT
, OVERRIDING
SYSTEM VALUE
est le comportement normal et le préciser ne
change rien mais PostgreSQL l'autorise
comme extension.)
OVERRIDING USER VALUE
Si cette clause est spécifiée, alors toute valeur fournir pour les colonnes d'identité 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, comme
si DEFAULT
était indiqué explicitement pour chaque
colonne. (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. Une colonne d'identité sera remplie avec une nouvelle valeur générée par la séquence associée. Pour une colonne générée, l'indiquer est permis mais ne fait que préciser le comportement normal du calcul de la colonne à partir de son expression de génération.
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.
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.
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é.
En cas de succès, la commande INSERT
renvoie un code de
la forme
INSERToid
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.
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.
Vous pouvez aussi considérer l'utilisation de MERGE
, vu
qu'elle mixe des commandes INSERT
,
UPDATE
et DELETE
en une seule
instruction. Voir MERGE.
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;
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
. Si vous préférez une instruction plus
conforme au standard SQL que ON CONFLICT
, voir MERGE.
Le standard SQL 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.