34. PostgreSQL : Langage DML

Ce chapitre décrit les instructions qui écrivent des données sur une instance MIRAJ en langage PostgreSQL : INSERT, UPDATE, DELETE, la clause RETURNING, INSERT … ON CONFLICT, TRUNCATE, COPY et les instructions préparées (PREPARE / EXECUTE). Il décrit le langage PostgreSQL tel que MIRAJ le parle : tous les exemples ont été exécutés sur une instance réelle et les résultats recopiés ; ce qui diffère de PostgreSQL est signalé au fil du texte et rassemblé en 34.11.

Les exemples s'appuient sur la base (le schéma) ventes et ses quatre tables :

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE TABLE client (
    id      serial PRIMARY KEY,
    nom     varchar(50) NOT NULL,
    ville   varchar(50),
    email   varchar(80) UNIQUE,
    solde   numeric(10,2) NOT NULL DEFAULT 0,
    cree_le timestamp NOT NULL DEFAULT now()
);

CREATE TABLE article (
    id      serial PRIMARY KEY,
    code    varchar(20) NOT NULL UNIQUE,
    libelle text NOT NULL,
    prix    numeric(10,2) NOT NULL CHECK (prix >= 0),
    stock   integer NOT NULL DEFAULT 0,
    actif   boolean NOT NULL DEFAULT true
);

CREATE TABLE commande (
    id        serial PRIMARY KEY,
    client_id integer NOT NULL REFERENCES client (id),
    date_cmd  date NOT NULL DEFAULT current_date,
    etat      varchar(12) NOT NULL DEFAULT 'ouverte'
);

CREATE TABLE ligne_commande (
    commande_id integer NOT NULL REFERENCES commande (id) ON DELETE CASCADE,
    article_id  integer NOT NULL REFERENCES article (id),
    quantite    integer NOT NULL CHECK (quantite > 0),
    prix_unit   numeric(10,2) NOT NULL,
    total       numeric(12,2) GENERATED ALWAYS AS (quantite * prix_unit) STORED,
    PRIMARY KEY (commande_id, article_id)
);

La définition des tables est décrite au chapitre 33. Langage DDL, les types et l'écriture des valeurs au chapitre 32. Types de données, les requêtes au chapitre 35. Requêtes SELECT.

Chaque écriture rend au client son étiquette de commande, comme PostgreSQL : INSERT 0 n, UPDATE n, DELETE n, COPY n, TRUNCATE TABLE. Les résultats de ce chapitre sont présentés à la manière de psql (NULL y est écrit en toutes lettres pour la lisibilité).

34.1 INSERT#

Synopsis#

INSERT INTO [schéma.]table [AS alias] [(colonne [, ...])]
    [OVERRIDING {SYSTEM | USER} VALUE]
    { VALUES ({expr | DEFAULT} [, ...]) [, (...) ...]
    | DEFAULT VALUES
    | requête
    | (requête) }
    [ON CONFLICT [cible] {DO NOTHING | DO UPDATE SET ... [WHERE condition]}]   -- 4.5
    [RETURNING élément [, ...]]                                                -- 4.4

requête est un SELECT (éventuellement précédé de WITH) ou une liste VALUES.

Une ligne, plusieurs lignes#

INSERT INTO client (nom, ville, email) VALUES ('Alice', 'Oran', '[email protected]');
-- INSERT 0 1

INSERT INTO client (nom, ville, email, solde) VALUES
    ('Bob', 'Alger', '[email protected]', 50.00),
    ('Chloé', 'Oran', NULL, 0),
    ('David', 'Constantine', '[email protected]', DEFAULT);
-- INSERT 0 3

SELECT id, nom, ville, email, solde FROM client ORDER BY id;

 id | nom   | ville       | email            | solde
----+-------+-------------+------------------+-------
 1  | Alice | Oran        | [email protected] | 0.00
 2  | Bob   | Alger       | [email protected]   | 50.00
 3  | Chloé | Oran        | NULL             | 0.00
 4  | David | Constantine | [email protected] | 0.00

Les tables d'exemple sont remplies ainsi pour la suite du chapitre :

INSERT INTO article (code, libelle, prix, stock) VALUES
    ('ST-01', 'Stylo bille', 1.20, 500),
    ('CA-01', 'Cahier 96 pages', 3.50, 200),
    ('CL-01', 'Classeur', 6.90, 80),
    ('AG-01', 'Agrafeuse', 14.00, 0);

INSERT INTO commande (client_id, date_cmd) VALUES
    (1, '2026-09-01'), (1, '2026-09-15'), (2, '2026-09-20');

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit) VALUES
    (1, 1, 10, 1.20), (1, 2, 2, 3.50), (2, 3, 1, 6.90), (3, 1, 100, 1.10);

• Le nom de la table peut être qualifié par son schéma (ventes.client) ; public.client désigne la table de la base courante.
• La liste de colonnes est facultative. Sans liste, les valeurs vont aux colonnes dans l'ordre de leur définition (une liste plus courte remplit les premières colonnes, voir plus bas).
• Une colonne absente de la liste reçoit sa valeur par défaut (DEFAULT de la définition, valeur suivante d'une colonne serial ou d'identité, NULL sinon).

DEFAULT et DEFAULT VALUES#

Le mot DEFAULT à la place d'une valeur demande la valeur par défaut de la colonne ; DEFAULT VALUES insère une ligne dont toutes les colonnes prennent leur valeur par défaut.

CREATE TABLE journal (id serial PRIMARY KEY, msg text DEFAULT 'vide', le timestamp DEFAULT now());

INSERT INTO journal DEFAULT VALUES;
INSERT INTO journal (id, msg) VALUES (DEFAULT, 'deux'), (DEFAULT, DEFAULT);
SELECT id, msg FROM journal ORDER BY id;

 id | msg
----+------
 1  | vide
 2  | deux
 3  | vide

DEFAULT VALUES échoue si une colonne NOT NULL n'a pas de valeur par défaut :

INSERT INTO client DEFAULT VALUES;
-- ERREUR 23502 : Field 'nom' doesn't have a default value

INSERT … SELECT#

Les lignes insérées viennent du résultat d'une requête :

CREATE TABLE gros_client (client_id integer PRIMARY KEY, total numeric(12,2));

INSERT INTO gros_client
SELECT c.client_id, sum(l.total)
FROM commande c JOIN ligne_commande l ON l.commande_id = c.id
GROUP BY c.client_id
HAVING sum(l.total) > 20;
-- INSERT 0 2

SELECT * FROM gros_client ORDER BY client_id;

 client_id | total
-----------+--------
 1         | 25.90
 2         | 110.00

La requête peut être écrite entre parenthèses, commencer par WITH et lire la table dans laquelle elle écrit : elle voit alors l'état d'avant l'instruction.

INSERT INTO gros_client (client_id, total)
WITH oran AS (SELECT id FROM client WHERE ville = 'Oran')
SELECT id, 0 FROM oran WHERE id NOT IN (SELECT client_id FROM gros_client);
-- INSERT 0 1 : le client 3 (Chloé)

INSERT INTO gros_client (SELECT 50, 1.5);
-- INSERT 0 1

La clause WITH s'écrit devant le SELECT, après INSERT INTO table, ou en tête de l'instruction, comme dans PostgreSQL ; ses expressions de table valent alors pour toute l'écriture, de même devant UPDATE et DELETE :

WITH oran AS (SELECT id FROM client WHERE ville = 'Oran')
INSERT INTO gros_client (client_id, total) SELECT id, 0 FROM oran;

WITH oran AS (SELECT id FROM client WHERE ville = 'Oran')
UPDATE commande SET etat = 'locale' WHERE client_id IN (SELECT id FROM oran);

WITH vides AS (SELECT id FROM commande WHERE id NOT IN (SELECT commande_id FROM ligne_commande))
DELETE FROM commande WHERE id IN (SELECT id FROM vides);

Une expression WITH peut écrire elle-même (expression de table modifiante) : WITH nom AS (INSERT | UPDATE | DELETE … RETURNING …) devant un SELECT, un INSERT, un UPDATE ou un DELETE. L'écriture s'exécute une seule fois, même citée plusieurs fois ou jamais ; son nom désigne les lignes de son RETURNING (sans RETURNING, elle s'exécute mais ne se lit pas).

-- File de travaux : prendre des lignes et les retirer d'un même geste
CREATE TABLE travail (id serial PRIMARY KEY, etat text, charge int);
INSERT INTO travail (etat, charge) VALUES ('attente', 5), ('fait', 3), ('attente', 8);

WITH pris AS (DELETE FROM travail WHERE etat = 'attente' RETURNING id, charge)
SELECT id, charge FROM pris ORDER BY id;

 id | charge
----+--------
 1  | 5
 3  | 8
(2 lignes)

-- Déplacer des lignes vers une archive
CREATE TABLE commande_archive (id integer, client_id integer, date_cmd date, etat varchar(12));
WITH vieux AS (DELETE FROM commande WHERE date_cmd < '2020-01-01' RETURNING *)
INSERT INTO commande_archive SELECT * FROM vieux;

Hors d'un bloc de transaction, l'instruction entière est atomique : l'échec de l'instruction principale annule les écritures. Limites : une écriture n'est admise que dans la clause WITH qui ouvre l'instruction (pas dans une sous-requête, une vue, CREATE TABLE … AS, EXPLAIN ni devant SELECT … INTO : 0A000) ; les écritures s'exécutent dans l'ordre écrit, avant l'instruction principale, qui voit donc les tables telles qu'elles les ont laissées (PostgreSQL lui montre leur état d'avant l'instruction).

Une liste VALUES sans liste de colonnes peut donner moins de valeurs que la table n'a de colonnes : elles vont aux premières colonnes, les suivantes prennent leur valeur par défaut (INSERT INTO gros_client VALUES (51) laisse total à sa valeur par défaut). Il en va de même d'un INSERT INTO t SELECT …. Plus de valeurs que de colonnes, des lignes de longueurs différentes, ou un nombre de valeurs qui ne correspond pas à une liste de colonnes écrite rendent 42601.

INSERT INTO table AS alias donne un alias à la table visée ; il sert dans ON CONFLICT … DO UPDATE … WHERE alias.colonne … (34.5) et dans RETURNING, et reste admis sans eux.

Avec une colonne serial, les numéros sont attribués dans l'ordre des lignes de la requête (ORDER BY compris).

Colonnes serial et d'identité#

Une colonne serial, bigserial ou GENERATED … AS IDENTITY reçoit le numéro suivant quand l'instruction ne lui donne pas de valeur, lui donne DEFAULT, ou lui donne NULL :

INSERT INTO client (nom) VALUES ('Émile') RETURNING id;          -- 5
INSERT INTO client (id, nom) VALUES (20, 'Fatima');              -- valeur écrite
INSERT INTO client (nom) VALUES ('Grace') RETURNING id;          -- 6
INSERT INTO client (id, nom) VALUES (NULL, 'Hana') RETURNING id; -- 7

À connaître :

• une valeur écrite n'avance pas le compteur, comme une séquence de PostgreSQL : après l'insertion de id = 20, la ligne suivante reçoit 6. Les numéros générés finissent par rencontrer la valeur écrite : l'insertion qui reçoit 20 échoue (23505, Duplicate entry '20' for key 'PRIMARY'). Comme un nextval, ce numéro est consommé malgré l'échec (dans une transaction aussi, même annulée ensuite) : l'insertion suivante reçoit 21 et réussit. Un numéro tiré par une insertion annulée (ROLLBACK) n'est pas non plus réutilisé ;
• NULL écrit dans une colonne serial ou GENERATED BY DEFAULT AS IDENTITY demande le numéro suivant (PostgreSQL : erreur 23502) ;
• GENERATED … AS IDENTITY (START WITH n) fixe le premier numéro :

  CREATE TABLE etiquette (id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 100) PRIMARY KEY, v text);
  INSERT INTO etiquette (v) VALUES ('a') RETURNING id;                  -- 100

• une colonne GENERATED ALWAYS AS IDENTITY refuse une valeur écrite, NULL compris (428C9), comme dans PostgreSQL ; seul DEFAULT y est admis, sauf sous OVERRIDING SYSTEM VALUE :

  CREATE TABLE ticket (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, objet text);
  INSERT INTO ticket (objet) VALUES ('a') RETURNING id;                 -- 1
  INSERT INTO ticket (id, objet) VALUES (DEFAULT, 'b') RETURNING id;    -- 2
  INSERT INTO ticket (id, objet) VALUES (10, 'c');
  -- ERREUR 428C9 : cannot insert a non-DEFAULT value into column "id"
  --   DÉTAIL : Column "id" is an identity column defined as GENERATED ALWAYS.
  --   CONSEIL : Use OVERRIDING SYSTEM VALUE to override.
  INSERT INTO ticket (id, objet) OVERRIDING SYSTEM VALUE VALUES (10, 'c') RETURNING id;  -- 10
  INSERT INTO ticket (id, objet) OVERRIDING USER VALUE VALUES (99, 'd') RETURNING id;    -- 3
  UPDATE ticket SET id = 50 WHERE objet = 'a';
  -- ERREUR 428C9 : column "id" can only be updated to DEFAULT
  --   DÉTAIL : Column "id" is an identity column defined as GENERATED ALWAYS.
  UPDATE ticket SET id = DEFAULT WHERE objet = 'a' RETURNING id;        -- numéro suivant (4)

  OVERRIDING SYSTEM VALUE garde la valeur écrite ; OVERRIDING USER VALUE l'ignore et attribue le numéro suivant pour une colonne d'identité, mais la garde dans une colonne serial, qui n'en est pas une (comme dans PostgreSQL). COPY … FROM STDIN écrit les valeurs du fichier, comme sous OVERRIDING SYSTEM VALUE. miraj-dump écrit les lignes d'une telle table en INSERT … OVERRIDING SYSTEM VALUE. La clause vaut aussi dans le corps d'une routine PL/pgSQL ou SQL. Sur le protocole PostgreSQL, le refus porte le détail et le conseil (Use OVERRIDING SYSTEM VALUE to override.) dans leurs champs, comme PostgreSQL ;

• un numéro attribué n'est jamais rendu : une instruction annulée par ROLLBACK laisse un trou dans la numérotation. Une instruction qui échoue (doublon, contrainte) consomme aussi les numéros qu'elle a tirés, comme nextval dans PostgreSQL ; le refus 428C9 d'une valeur écrite dans une colonne GENERATED ALWAYS, levé avant tout tirage, n'en consomme aucun ;
• le numéro attribué se lit par RETURNING (34.4), ou par lastval(), qui rend la dernière valeur servie à la session par une colonne serial / IDENTITY ou par nextval (55000 tant que la session n'en a reçu aucune). Il n'y a pas de séquence client_id_seq derrière une colonne serial : currval('client_id_seq') rend 42P01.

Colonnes générées#

Une colonne GENERATED ALWAYS AS (expr) STORED est calculée par MIRAJ. Elle s'omet, ou reçoit DEFAULT ; une valeur écrite pour elle est refusée, comme dans PostgreSQL :

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit, total)
VALUES (3, 2, 1, 3.50, 999);
-- ERREUR 428C9 : cannot insert a non-DEFAULT value into column "total"
--   DÉTAIL : Column "total" is a generated column.

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit)
VALUES (3, 2, 1, 3.50);
INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit, total)
VALUES (3, 3, 2, 6.90, DEFAULT);
SELECT * FROM ligne_commande WHERE commande_id = 3 ORDER BY article_id;

 commande_id | article_id | quantite | prix_unit | total
-------------+------------+----------+-----------+--------
 3           | 1          | 100      | 1.10      | 110.00
 3           | 2          | 1        | 3.50      | 3.50
 3           | 3          | 2        | 6.90      | 13.80

Valeurs tirées d'une séquence#

Une séquence (voir 33. Langage DDL) fournit une valeur par le DEFAULT de la colonne ou directement dans l'instruction :

CREATE SEQUENCE seq_facture START WITH 1000;
CREATE TABLE facture (n bigint PRIMARY KEY DEFAULT nextval('seq_facture'), client_id integer);

INSERT INTO facture (client_id) VALUES (1), (2) RETURNING n;                    -- 1000, 1001
INSERT INTO facture (n, client_id) VALUES (nextval('seq_facture'), 3) RETURNING n;   -- 1002
SELECT currval('seq_facture');                                                  -- 1002

Écriture des valeurs#

Les valeurs s'écrivent avec la lexicographie de PostgreSQL : chaîne standard 'l''été' (la barre oblique y est un caractère ordinaire), chaîne à échappements E'a\tb', chaîne entre dollars $$l'été "x"$$. Une valeur est convertie au type de la colonne :

INSERT INTO article (code, libelle, prix, stock, actif) VALUES
    ('GO-01', 'Gomme', 0.999, '12', 'yes'),
    ('RE-01', 'Règle 30 cm', 2, 2.7, 'f')
RETURNING code, prix, stock, actif;

 code  | prix | stock | actif
-------+------+-------+-------
 GO-01 | 1.00 | 12    | t
 RE-01 | 2.00 | 3     | f

• un décimal est arrondi à l'échelle de la colonne (0.999 → 1.00), un décimal écrit dans une colonne entière est arrondi (2.7 → 3) ;
• un texte est lu selon le type de la colonne ('12' dans un entier, '2026-09-01' dans une date) ; une colonne boolean lit 't', 'true', 'yes', 'on', '1' et leurs contraires ;
• une valeur qui ne se convertit pas, trop longue ou hors des bornes du type est une erreur : rien n'est tronqué en silence (l'instance d'exemple est démarrée avec STRICT_TRANS_TABLES, voir 31. Prise en main).

Contraintes#

Les contraintes sont vérifiées à l'écriture de chaque ligne. Une instruction est tout ou rien : la première erreur annule toutes ses lignes.

INSERT INTO gros_client VALUES (60, 1), (61, 2), (60, 3);
-- ERREUR 23505 : Duplicate entry '60' for key 'PRIMARY'
SELECT count(*) AS n FROM gros_client WHERE client_id >= 60;    -- 0

Les deux refus 23502 valent quel que soit sql_mode, même sans STRICT_TRANS_TABLES, pour INSERT, UPDATE et ON CONFLICT … DO UPDATE : en langage PostgreSQL, un NULL écrit dans une colonne NOT NULL n'est jamais remplacé par la valeur implicite du type.

Les SQLSTATE sont ceux de PostgreSQL. Les messages nomment les objets entre guillemets doubles et parlent de schema, relation et role ; les plus courants reprennent le texte de PostgreSQL (relation "…" does not exist, new row for relation "…" violates check constraint "…"), les autres gardent celui de MIRAJ (en anglais). Une application doit donc se fier au SQLSTATE, pas au texte.

Unicité et casse. Les textes sont comparés octet par octet (collation « C », comme PostgreSQL), y compris dans les contraintes d'unicité : '[email protected]' n'est pas un doublon de '[email protected]'.

INSERT INTO client (nom, email) VALUES ('Alice Bis', '[email protected]') RETURNING id, email;
-- 8 | [email protected]
DELETE FROM client WHERE nom = 'Alice Bis';                       -- DELETE 1

34.2 UPDATE#

Synopsis#

UPDATE [schéma.]table [[AS] alias]
    SET { colonne = {expr | DEFAULT}
        | (colonne [, ...]) = [ROW] ({expr | DEFAULT} [, ...]) } [, ...]
    [FROM source [, ...]]
    [WHERE condition]
    [RETURNING élément [, ...]]                                                -- 4.4

Forme simple#

UPDATE client SET solde = solde + 20 WHERE ville = 'Oran';        -- UPDATE 2
UPDATE client SET solde = solde WHERE ville = 'Oran';             -- UPDATE 2
UPDATE client SET solde = solde + 20 WHERE ville = 'Tlemcen';     -- UPDATE 0

Le nombre de l'étiquette est celui des lignes retenues par WHERE, qu'elles changent de valeur ou non, comme PostgreSQL. Sans WHERE, toutes les lignes sont modifiées.

La table peut porter un alias ; une colonne à gauche du = peut être qualifiée par la table ou par l'alias :

UPDATE client AS c SET solde = c.solde + 1, ville = 'Blida' WHERE c.id = 4;
UPDATE client SET solde = DEFAULT, ville = NULL WHERE id = 4;     -- valeur par défaut, NULL

Affectation de plusieurs colonnes#

UPDATE client SET (ville, solde) = ('Constantine', 10) WHERE id = 4;
UPDATE client SET (ville, solde) = ROW ('Constantine', 11) WHERE id = 4;

La liste de droite est une liste de valeurs ; une sous-requête à cet endroit (SET (a, b) = (SELECT …)) n'est pas prise en charge (0A000).

Les expressions lisent les anciennes valeurs#

Comme dans PostgreSQL, toutes les expressions de SET sont calculées sur la ligne d'avant la modification, quel que soit leur ordre :

CREATE TABLE paire (id integer PRIMARY KEY, a integer, b integer);
INSERT INTO paire VALUES (1, 10, 20), (2, 30, 40);

UPDATE paire SET a = b, b = a WHERE id = 1;         -- échange les deux colonnes
UPDATE paire SET a = 100, b = a + 1 WHERE id = 2;   -- b = ancien a + 1
SELECT * FROM paire ORDER BY id;

 id | a   | b
----+-----+----
 1  | 20  | 10
 2  | 100 | 31

Sous-requêtes#

Une sous-requête peut fournir une valeur (elle peut être corrélée à la ligne modifiée) ou désigner les lignes à modifier. Si elle lit la table modifiée, elle voit l'état d'avant l'instruction.

UPDATE client
SET solde = (SELECT count(*) FROM commande WHERE commande.client_id = client.id)
WHERE id <= 3;                                                    -- UPDATE 3

UPDATE client SET solde = (SELECT max(solde) FROM client) WHERE id = 3;

UPDATE article SET actif = false
WHERE id NOT IN (SELECT article_id FROM ligne_commande);

Une expression WITH s'écrit dans la sous-requête (WHERE id IN (WITH x AS (…) SELECT id FROM x)) ou devant le mot UPDATE (WITH x AS (…) UPDATE …, voir 34.1).

UPDATE ONLY table, DELETE FROM ONLY table et table * sont acceptés : ONLY et * sont sans effet, MIRAJ n'ayant pas d'héritage de tables.

UPDATE … FROM : valeurs prises dans d'autres tables#

La clause FROM joint la table modifiée à d'autres sources ; la condition de jointure s'écrit dans WHERE. Seule la table qui suit UPDATE est modifiée.

UPDATE client SET solde = client.solde + g.total
FROM gros_client g
WHERE g.client_id = client.id;                                    -- UPDATE 3

Les sources peuvent être plusieurs tables séparées par des virgules, des jointures explicites ou une table dérivée :

-- table dérivée : total des lignes de commande par client
UPDATE client c SET solde = c.solde - s.total
FROM (SELECT co.client_id, sum(l.total) AS total
      FROM commande co JOIN ligne_commande l ON l.commande_id = co.id
      GROUP BY co.client_id) s
WHERE s.client_id = c.id;                                         -- UPDATE 2

-- jointure explicite dans FROM
UPDATE article a SET stock = a.stock - l.quantite
FROM commande co JOIN ligne_commande l ON l.commande_id = co.id
WHERE l.article_id = a.id AND co.id = 1;                          -- UPDATE 2

-- liste de tables
UPDATE article a SET stock = a.stock + l.quantite
FROM commande co, ligne_commande l
WHERE l.commande_id = co.id AND l.article_id = a.id AND co.id = 1;

Une ligne de la table modifiée appariée à plusieurs lignes de la source est modifiée et comptée une seule fois (UPDATE paire … FROM (VALUES (1), (1), (2)) AS v (x) WHERE v.x = paire.id : UPDATE 2) ; la ligne source retenue n'est pas définie, comme dans PostgreSQL.

Une table peut être jointe à elle-même sous un second alias (UPDATE t SET b = t2.b FROM t t2 WHERE t2.a = t.a + 1).

Colonnes générées, contraintes#

Une colonne générée n'est pas affectable : seul DEFAULT y est admis. Elle est recalculée quand ses opérandes changent.

UPDATE ligne_commande SET total = 5 WHERE commande_id = 1;
-- ERREUR 428C9 : column "total" can only be updated to DEFAULT
--   DÉTAIL : Column "total" is a generated column.
UPDATE ligne_commande SET total = DEFAULT WHERE commande_id = 1;  -- UPDATE 2
UPDATE ligne_commande SET quantite = 3 WHERE commande_id = 2 RETURNING quantite, total;
-- 3 | 20.70

Les contraintes sont vérifiées comme pour un INSERT, et la première erreur annule toute l'instruction :

34.3 DELETE#

Synopsis#

DELETE FROM [schéma.]table [[AS] alias]
    [USING source [, ...]]
    [WHERE condition]
    [RETURNING élément [, ...]]                                                -- 4.4

Forme simple#

DELETE FROM paire WHERE id = 2;                 -- DELETE 1
DELETE FROM paire WHERE id = 99;                -- DELETE 0
DELETE FROM paire AS p WHERE p.a > 100;         -- alias
DELETE FROM gros_client WHERE total < 2;        -- DELETE 2

Les sous-requêtes s'emploient comme dans un UPDATE, corrélées ou non :

DELETE FROM gros_client
WHERE NOT EXISTS (SELECT 1 FROM commande co WHERE co.client_id = gros_client.client_id);   -- DELETE 0

DELETE FROM gros_client;                        -- toutes les lignes : DELETE 2

DELETE FROM table sans condition garde le compteur d'une colonne serial, à la différence de TRUNCATE (34.6) :

DELETE FROM journal;                                               -- DELETE 3
INSERT INTO journal (msg) VALUES ('après DELETE') RETURNING id;    -- 4

DELETE … USING : supprimer d'après d'autres tables#

USING joint la table vidée à d'autres sources, la condition de jointure s'écrivant dans WHERE. Seule la table qui suit DELETE FROM perd des lignes.

INSERT INTO commande (client_id, date_cmd) VALUES (3, '2026-10-01');          -- commande 4
INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit)
VALUES (4, 4, 1, 14.00), (4, 2, 1, 3.50);

-- les lignes « article 4 » des commandes du client 3
DELETE FROM ligne_commande l
USING commande co
WHERE co.id = l.commande_id AND co.client_id = 3 AND l.article_id = 4;       -- DELETE 1

-- plusieurs sources, en liste ou en jointure explicite
DELETE FROM ligne_commande l
USING commande co, client c
WHERE co.id = l.commande_id AND c.id = co.client_id AND c.ville = 'Tlemcen';

DELETE FROM ligne_commande l
USING commande co JOIN client c ON c.id = co.client_id
WHERE co.id = l.commande_id AND c.ville = 'Tlemcen';

La table peut être jointe à elle-même (DELETE FROM t USING t t2 WHERE t2.a = t.a + 1).

Clés étrangères#

Les actions ON DELETE des clés étrangères s'appliquent. Une ligne encore référencée est protégée (23503) ; ON DELETE CASCADE supprime les lignes enfants, sans les compter dans l'étiquette :

DELETE FROM client WHERE id = 1;
-- ERREUR 23503 : Cannot delete or update a parent row: a foreign key constraint fails
--   ("ventes"."commande", CONSTRAINT "commande_ibfk_1" FOREIGN KEY ("client_id") REFERENCES "client" ("id"))

DELETE FROM commande WHERE id = 4;       -- DELETE 1 : ses lignes de commande partent avec elle

Les contraintes sont vérifiées immédiatement, ligne par ligne (DEFERRABLE et INITIALLY DEFERRED sont acceptés à la création, sans effet).

34.4 RETURNING#

INSERT, UPDATE et DELETE acceptent une clause RETURNING, écrite comme la liste d'un SELECT : *, table.*, colonnes (qualifiées ou non par la table ou son alias), expressions, alias. L'instruction rend une ligne par ligne écrite, dans l'ordre des écritures, puis son étiquette habituelle.

INSERT INTO client (nom, ville) VALUES ('Jamel', 'Oran'), ('Karim', 'Alger')
RETURNING id, nom, solde;

 id | nom   | solde
----+-------+-------
 9  | Jamel | 0.00
 10 | Karim | 0.00
(2 lignes)
INSERT 0 2

INSERT INTO client (nom) VALUES ('Lina') RETURNING *;

 id | nom  | ville | email | solde | cree_le
----+------+-------+-------+-------+----------------------------
 11 | Lina | NULL  | NULL  | 0.00  | 2026-10-19 03:27:59.301623

INSERT INTO client (nom) VALUES ('Mona') RETURNING id * 100 AS grand, upper(nom) AS maj;

 grand | maj
-------+------
 1200  | MONA

Valeurs rendues#

• INSERT et UPDATE rendent les valeurs après l'écriture : numéro d'une colonne serial ou d'identité, valeurs par défaut, colonnes générées, valeurs converties au type de la colonne.
• DELETE rend les valeurs d'avant la suppression.
• Avec ON CONFLICT (34.5), seules les lignes insérées ou modifiées sont rendues ; une ligne écartée par DO NOTHING ou par un WHERE faux ne l'est pas.
• Une instruction qui n'écrit aucune ligne rend un résultat vide, avec ses colonnes.

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit)
VALUES (2, 1, 3, 1.20) RETURNING quantite, prix_unit, total;       -- 3 | 1.20 | 3.60

UPDATE article SET stock = stock - 1 WHERE code = 'ST-01' RETURNING stock;   -- 499

UPDATE article SET prix = round(prix * 1.10, 2) WHERE stock > 100
RETURNING code, prix AS nouveau_prix;

 code  | nouveau_prix
-------+--------------
 ST-01 | 1.32
 CA-01 | 3.85
(2 lignes)
UPDATE 2

INSERT INTO client (nom) SELECT nom || ' bis' FROM client WHERE id <= 2 ORDER BY id
RETURNING id, nom;                               -- 13 | Alice bis, 14 | Bob bis

DELETE FROM client WHERE nom LIKE '% bis' RETURNING id, nom;

 id | nom
----+-----------
 13 | Alice bis
 14 | Bob bis
(2 lignes)
DELETE 2

Règles#

• Nom des colonnes : une expression sans alias porte le texte écrit (RETURNING id + 1, upper(nom) nomme ses colonnes id + 1 et upper(nom) ; PostgreSQL les nommerait ?column? et upper). Donner un alias à toute expression lue par son nom.
• Vérification avant l'écriture : une colonne inconnue dans la clause est refusée (42703, Unknown column 'inconnue' in 'returning list') sans que rien ne soit écrit.
• Sous-requête : une sous-requête non corrélée est admise ; elle voit l'état d'avant l'instruction, comme PostgreSQL.

  SELECT count(*) AS n FROM client;                                          -- 12
  INSERT INTO client (nom) VALUES ('P') RETURNING (SELECT count(*) FROM client) AS avant;
  -- 12 : la ligne insérée n'est pas comptée

• Agrégat dans la clause (RETURNING count(*)) : 42803.
• Droits : en plus du droit d'écrire, le compte doit avoir le droit SELECT sur la table ; sinon 42501 (permission denied for table …). Voir 38. Comptes et privilèges.
• Pilotes : les lignes de la clause se lisent comme celles d'un SELECT (cursor.fetchone() de psycopg2 après un INSERT … RETURNING).
• Dans une transaction, un ROLLBACK annule les lignes déjà rendues au client.

RETURNING est le moyen de lire un numéro serial attribué :

cur.execute("INSERT INTO client (nom, ville) VALUES (%s, %s) RETURNING id", ("Rachid", "Oran"))
nouvel_id = cur.fetchone()[0]

Non pris en charge#

34.5 INSERT … ON CONFLICT#

ON CONFLICT décide du sort d'une ligne proposée qui heurte une contrainte d'unicité : l'écarter (DO NOTHING) ou modifier la ligne existante (DO UPDATE, l'« upsert »).

Synopsis#

INSERT INTO table [AS alias] [(colonne [, ...])] {VALUES ... | requête}
    ON CONFLICT [ (colonne [, ...]) | ON CONSTRAINT nom_de_contrainte ]
    { DO NOTHING
    | DO UPDATE SET { colonne = {expr | DEFAULT}
                    | (colonne [, ...]) = ({expr | DEFAULT} [, ...]) } [, ...]
                [WHERE condition] }
    [RETURNING élément [, ...]]

Les exemples utilisent une table de compteurs :

CREATE TABLE compteur (
    code text PRIMARY KEY,
    n    integer NOT NULL DEFAULT 0,
    lib  varchar(20) UNIQUE,
    maj  timestamp
);
INSERT INTO compteur (code, n, lib) VALUES ('a', 1, 'premier');

DO NOTHING#

INSERT INTO compteur (code, n) VALUES ('a', 9) ON CONFLICT DO NOTHING;                   -- INSERT 0 0
INSERT INTO compteur (code, n) VALUES ('a', 9) ON CONFLICT (code) DO NOTHING;            -- INSERT 0 0
INSERT INTO compteur (code, n) VALUES ('a', 9)
    ON CONFLICT ON CONSTRAINT compteur_pkey DO NOTHING;                                  -- INSERT 0 0

INSERT INTO compteur (code, n) VALUES ('a', 9), ('b', 2)
    ON CONFLICT (code) DO NOTHING RETURNING code, n;

 code | n
------+---
 b    | 2
(1 ligne)
INSERT 0 1

La ligne en conflit est écartée sans erreur ni avis ; les autres lignes de l'instruction sont insérées. Deux lignes proposées de même clé dans la même instruction : la première est insérée, la seconde écartée.

DO UPDATE#

Dans SET et WHERE :

• une colonne seule, ou qualifiée par le nom de la table ou par son alias (INSERT INTO t AS x), désigne la ligne existante ;
• EXCLUDED.colonne désigne la ligne proposée (valeurs par défaut comprises) ;
• DEFAULT remet la valeur par défaut de la colonne.

INSERT INTO compteur AS x (code, n) VALUES ('a', 5), ('c', 3)
ON CONFLICT (code) DO UPDATE SET n = x.n + EXCLUDED.n, maj = '2026-10-11 10:00:00'
RETURNING x.code, x.n, x.maj;

 code | n | maj
------+---+---------------------
 a    | 6 | 2026-10-11 10:00:00
 c    | 3 | NULL
(2 lignes)
INSERT 0 2

a existait (1 + 5 = 6), c a été insérée. Les trois écritures du nom de la ligne existante sont équivalentes :

... DO UPDATE SET n = x.n + EXCLUDED.n          -- alias de INSERT INTO compteur AS x
... DO UPDATE SET n = compteur.n + EXCLUDED.n   -- nom de la table
... DO UPDATE SET n = n + EXCLUDED.n            -- colonne seule

WHERE laisse la ligne existante telle quelle quand la condition n'est pas vraie ; la ligne n'est alors ni comptée, ni rendue par RETURNING :

-- compteur contient a = 16, b = 2
INSERT INTO compteur AS x (code, n) VALUES ('a', 1000), ('b', 1)
ON CONFLICT (code) DO UPDATE SET n = EXCLUDED.n WHERE x.n < 10
RETURNING code, n;

 code | n
------+---
 b    | 1
(1 ligne)
INSERT 0 1

Autres formes vérifiées :

-- plusieurs colonnes d'un coup
INSERT INTO compteur (code, n) VALUES ('c', 5)
ON CONFLICT (code) DO UPDATE SET (n, maj) = (EXCLUDED.n, NULL);

-- valeur par défaut
INSERT INTO compteur (code, n) VALUES ('c', 5) ON CONFLICT (code) DO UPDATE SET n = DEFAULT;

-- source SELECT
INSERT INTO compteur (code, n) SELECT code, 1 FROM compteur c2
ON CONFLICT (code) DO UPDATE SET n = compteur.n + 1;

-- clé de plusieurs colonnes ; la colonne générée est recalculée
INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit) VALUES (1, 1, 5, 1.20)
ON CONFLICT (commande_id, article_id)
DO UPDATE SET quantite = ligne_commande.quantite + EXCLUDED.quantite
RETURNING quantite, total;                                         -- 15 | 18.00

La cible : quelle contrainte arbitre#

Noms de contraintes. La clé primaire se nomme <table>_pkey, comme dans PostgreSQL. Une contrainte UNIQUE sans nom déclarée sur une colonne porte, dans MIRAJ, le nom de cette colonne (lib), et non <table>_<colonne>_key :

INSERT INTO compteur (code, lib) VALUES ('z', 'premier')
ON CONFLICT ON CONSTRAINT lib DO UPDATE SET n = 77 RETURNING code, n;       -- a | 77

INSERT INTO compteur (code, lib) VALUES ('z', 'premier')
ON CONFLICT ON CONSTRAINT compteur_lib_key DO NOTHING;
-- ERREUR 42704 : constraint "compteur_lib_key" for table "compteur" does not exist

Pour un nom stable, nommer la contrainte à la création (CONSTRAINT compteur_lib_key UNIQUE (lib)) ou désigner la cible par ses colonnes. Les noms réels se lisent dans pg_constraint (voir 40. Catalogue et outils).

Seul l'arbitre est traité. Une ligne qui heurte une autre contrainte d'unicité que la cible reçoit l'erreur habituelle, comme dans PostgreSQL :

-- 'z' est libre, mais lib = 'premier' existe déjà
INSERT INTO compteur (code, lib) VALUES ('z', 'premier') ON CONFLICT (code) DO NOTHING;
-- ERREUR 23505 : Duplicate entry 'premier' for key 'lib'

INSERT INTO compteur (code, lib) VALUES ('z', 'premier') ON CONFLICT (lib) DO NOTHING;   -- INSERT 0 0
INSERT INTO compteur (code, lib) VALUES ('z', 'premier') ON CONFLICT DO NOTHING;         -- INSERT 0 0

Règles#

• Compte : l'étiquette INSERT 0 n compte les lignes insérées et les lignes modifiées ; une ligne en conflit modifiée sans changement de valeur compte aussi. DO NOTHING et un WHERE faux ne comptent pas.
• Une ligne ne peut pas être touchée deux fois par la même instruction DO UPDATE : deux lignes proposées de même clé donnent 21000 et rien n'est écrit.

  INSERT INTO compteur (code, n) VALUES ('d', 1), ('d', 2) ON CONFLICT (code) DO UPDATE SET n = EXCLUDED.n;
  -- ERREUR 21000 : ON CONFLICT DO UPDATE command cannot affect row a second time

• DO UPDATE exige une cible : ON CONFLICT DO UPDATE … rend 42601 (ON CONFLICT DO UPDATE requires inference specification or constraint name).
• Les autres erreurs restent des erreurs : NOT NULL (23502), CHECK, clé étrangère, conversion.
• Casse : les clés de texte sont comparées octet par octet ; 'A' n'est pas en conflit avec 'a' et s'insère comme une nouvelle ligne (INSERT INTO compteur (code, n) VALUES ('A', 7) ON CONFLICT (code) DO UPDATE SET n = EXCLUDED.n → INSERT 0 1, ligne A ajoutée).
• Droits : INSERT, et UPDATE pour DO UPDATE (42501 sinon, même quand aucune ligne n'est en conflit).

Non pris en charge#

34.6 TRUNCATE#

TRUNCATE [TABLE] [ONLY] [schéma.]table
    [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]

TRUNCATE vide une table d'un seul bloc, sans passer ligne par ligne (les déclencheurs DELETE ne s'exécutent pas). L'étiquette est TRUNCATE TABLE.

TRUNCATE journal;
INSERT INTO journal (msg) VALUES ('après TRUNCATE') RETURNING id;      -- 5 : le compteur continue

TRUNCATE journal RESTART IDENTITY;
INSERT INTO journal (msg) VALUES ('après RESTART') RETURNING id;       -- 1

À connaître en venant de PostgreSQL :

• Compteur d'identité : comme dans PostgreSQL, il continue (CONTINUE IDENTITY, le défaut) et repart de 1 avec RESTART IDENTITY, hors d'un bloc comme dans un bloc. Dans un bloc, un ROLLBACK (ou ROLLBACK TO SAVEPOINT) lui rend sa valeur d'avant.
• Une table à la fois : TRUNCATE journal, compteur rend 0A000 (TRUNCATE of several tables is not supported yet in the PostgreSQL language).

• Dans un bloc de transaction, TRUNCATE est transactionnel, comme dans PostgreSQL : ROLLBACK et ROLLBACK TO SAVEPOINT rendent les lignes, et les autres sessions ne voient la table vide qu'au COMMIT.

  BEGIN;
  TRUNCATE journal;
  SELECT count(*) AS n FROM journal;      -- 0
  ROLLBACK;
  SELECT count(*) AS n FROM journal;      -- les lignes sont revenues

  Comme dans PostgreSQL, TRUNCATE verrouille la table (et, avec CASCADE, celles qu'il vide aussi) en ACCESS EXCLUSIVE jusqu'à la fin de la transaction : il attend d'abord les autres transactions qui y tiennent des lignes (ou qui l'ont lue, dans le modèle de concurrence par défaut), puis toute autre session qui lit ou écrit la table attend le COMMIT ou le ROLLBACK — au plus lock_timeout, puis 55P03 (37.6). Deux transactions qui s'attendent l'une l'autre : 40P01 pour l'une des deux.

  La table y est vidée d'un seul bloc, comme hors d'un bloc, en un temps qui ne dépend pas du nombre de lignes (sans déclencheur ni action de clé étrangère ; une table versionnée perd aussi son historique) : son contenu est mis de côté jusqu'à la fin de la transaction, rendu par ROLLBACK ou par un ROLLBACK TO SAVEPOINT d'un point posé avant le TRUNCATE, libéré au COMMIT. Les écritures faites par la transaction avant le TRUNCATE disparaissent avec lui, celles d'après restent ; deux TRUNCATE de suite et les clés uniques réinsérées après lui se comportent comme dans PostgreSQL. Une table disque (ENGINE = disk) est vidée en bloc elle aussi : son fichier reste intact jusqu'au COMMIT, le contenu neuf s'écrit dans un fichier neuf au point de sauvegarde suivant ; une table partitionnée de l'édition Cluster voit chacun de ses segments vidé en bloc. Seule une table partitionnée dont une partition est tenue par un autre nœud voit encore ses lignes supprimées une à une sous la transaction.

• Droits : TRUNCATE exige le droit DROP sur la table (42501 : permission denied for table …) ; il n'y a pas de droit TRUNCATE distinct.

Tables référencées#

Une table référencée par une clé étrangère ne se vide pas seule ; CASCADE vide aussi, de proche en proche, les tables qui la référencent :

CREATE TABLE parent (id integer PRIMARY KEY);
CREATE TABLE enfant (id integer PRIMARY KEY, parent_id integer REFERENCES parent (id));
INSERT INTO parent VALUES (1), (2);
INSERT INTO enfant VALUES (10, 1);

TRUNCATE parent;              -- ou TRUNCATE parent RESTRICT
-- ERREUR 0A000 : Cannot truncate a table referenced in a foreign key constraint
--   ("ventes"."enfant", CONSTRAINT "enfant_ibfk_1" FOREIGN KEY ("parent_id") REFERENCES "parent" ("id"))

TRUNCATE parent CASCADE;      -- TRUNCATE TABLE : parent et enfant sont vides

34.7 COPY#

COPY charge ou extrait des lignes en masse, au format texte ou CSV. Le chargement passe par la connexion du client (FROM STDIN) ; l'extraction passe par la connexion (TO STDOUT) ou s'écrit dans un fichier du serveur (TO '/chemin', voir plus bas).

Synopsis#

COPY table [(colonne [, ...])] FROM STDIN [[WITH] (option [, ...])]
COPY table [(colonne [, ...])] TO {STDOUT | 'fichier'} [[WITH] (option [, ...])]
COPY (requête) TO {STDOUT | 'fichier'} [[WITH] (option [, ...])]

La forme ancienne des options est aussi acceptée : WITH DELIMITER AS '|' NULL AS '', WITH CSV HEADER, CSV.

Côté client, COPY s'emploie par \copy dans psql (qui lit ou écrit un fichier du poste client : \copy tarif from 'tarif.csv' with (format csv, header)), par copy_expert, copy_from et copy_to de psycopg2 :

with open("tarif.csv", encoding="utf-8") as f:
    cur.copy_expert("COPY tarif (code, libelle, prix) FROM STDIN WITH (FORMAT csv, HEADER)", f)
print(cur.rowcount)        # nombre de lignes chargées

Les exemples utilisent cette table :

CREATE TABLE tarif (
    id      serial PRIMARY KEY,
    code    varchar(20) NOT NULL UNIQUE,
    libelle text,
    prix    numeric(10,2) DEFAULT 1,
    actif   boolean DEFAULT true,
    depuis  date,
    ttc     numeric(12,2) GENERATED ALWAYS AS (prix * 1.19) STORED
);

Charger : COPY … FROM STDIN#

Format texte. Un enregistrement par ligne, champs séparés par une tabulation (notée → ci-dessous), NULL écrit \N, échappements \t, \n, \r, \\.

COPY tarif (code, libelle, prix, actif, depuis) FROM STDIN;
ST-01→Stylo bille→1.20→t→2026-01-05
CA-01→Cahier\t96 pages→3.50→f→\N
CL-01→\N→\N→true→2026-03-01
\.

COPY 3. Dans psql, la ligne \. termine les données ; un pilote les termine en fermant le flux.

SELECT * FROM tarif ORDER BY id;

 id | code  | libelle          | prix | actif | depuis     | ttc
----+-------+------------------+------+-------+------------+------
 1  | ST-01 | Stylo bille      | 1.20 | t     | 2026-01-05 | 1.43
 2  | CA-01 | Cahier\t96 pages | 3.50 | f     | NULL       | 4.17
 3  | CL-01 | NULL             | NULL | t     | 2026-03-01 | NULL

(le libellé de la ligne 2 contient une vraie tabulation, montrée ici \t.)

• Colonnes absentes de la liste : elles reçoivent leur valeur par défaut (numéro serial, DEFAULT), comme dans un INSERT. Un \N explicite, lui, écrit NULL, même si la colonne a une valeur par défaut (prix de la ligne 3).
• Sans liste de colonnes, les données portent toutes les colonnes de la table dans leur ordre, sauf les colonnes générées (ici six champs, ttc exclu). Une valeur donnée à la colonne serial est gardée, sans avancer son compteur.
• Une colonne générée nommée dans la liste est refusée (428C9 : cannot insert a non-DEFAULT value into column "ttc"), comme dans un INSERT.

Format CSV. Champs séparés par une virgule, cités par des guillemets doubles quand ils contiennent le séparateur, un guillemet (doublé : "") ou une fin de ligne. Un champ vide non cité est NULL ; "" est la chaîne vide.

COPY tarif (code, libelle, prix) FROM STDIN WITH (FORMAT csv, HEADER);
code,libelle,prix
C1,"Benali, Amine",31
C2,"Le ""Grand""",12.5
C3,,
C4,"",7
C5,"deux
lignes",1
\.

SELECT code, libelle, libelle IS NULL AS sans_libelle, prix FROM tarif WHERE code LIKE 'C_' ORDER BY code;

 code | libelle       | sans_libelle | prix
------+---------------+--------------+-------
 C1   | Benali, Amine | f            | 31.00
 C2   | Le "Grand"    | f            | 12.50
 C3   | NULL          | t            | NULL
 C4   |               | f            | 7.00
 C5   | deux\nlignes  | f            | 1.00

Options de format, vérifiées :

COPY tarif (code, libelle, prix) FROM STDIN
    WITH (FORMAT csv, DELIMITER ';', NULL 'NUL', QUOTE '''', HEADER true);
code;libelle;prix
D1;'a;b';NUL
D2;NUL;2
\.
-- D1 : libelle = a;b, prix NULL     D2 : libelle NULL, prix = 2.00

COPY tarif (code, libelle) FROM STDIN WITH DELIMITER AS '|' NULL AS '';
E1|x
E2|
\.
-- E2 : libelle NULL

COPY tarif (code, libelle) FROM STDIN (FORMAT csv, ESCAPE '\');
I1,"a\"b"
\.
-- libelle = a"b

Valeurs lues. Les conversions sont celles d'un INSERT :

• booléens écrits comme PostgreSQL (t, true, yes, on, 1, f, no…) ;
• bytea en hexadécimal (\\x00ff dans le format texte, où la barre oblique se double) ;
• un uuid est gardé sous sa forme canonique en minuscules ;
• une date-heure écrite avec un décalage (2026-10-06 10:00:00+02) perd ce décalage dans une colonne timestamp (2026-10-06 10:00:00) et désigne son instant exact dans une colonne timestamptz (relue 2026-10-06 09:00:00+01 dans une session à UTC+1).

Tout ou rien. Un COPY … FROM STDIN est une seule instruction : il s'exécute comme un INSERT (valeurs par défaut, contraintes, clés étrangères, déclencheurs) et une erreur, où qu'elle soit dans les données, n'écrit aucune ligne. Dans un bloc de transaction, il est annulé par ROLLBACK. Les données sont gardées en mémoire par le serveur jusqu'à la fin de l'envoi, puis chargées : prévoir cette mémoire pour un très gros chargement, ou le découper.

Le numéro de ligne n'est donné que pour les erreurs de format (22P04, booléen) ; les erreurs de conversion et de contrainte citent la colonne.

Extraire : COPY … TO STDOUT#

COPY tarif TO STDOUT;

1→ST-01→Stylo bille→1.20→t→2026-01-05
2→CA-01→Cahier\t96 pages→3.50→f→\N
3→CL-01→\N→\N→t→2026-03-01
4→C1→Benali, Amine→31.00→t→\N
5→C2→Le "Grand"→12.50→t→\N
6→C3→\N→\N→t→\N
7→C4→→7.00→t→\N
8→C5→deux\nlignes→1.00→t→\N

COPY 8. Sans liste de colonnes, les colonnes générées ne sont pas écrites (ttc est absente) : le résultat se recharge tel quel par COPY tarif FROM STDIN. Les valeurs sont celles qu'un SELECT rend en texte (t / f, dates ISO, bytea en \x…).

COPY tarif (code, libelle, prix, actif) TO STDOUT WITH (FORMAT csv, HEADER);

code,libelle,prix,actif
ST-01,Stylo bille,1.20,t
CA-01,Cahier	96 pages,3.50,f
CL-01,,,t
C1,"Benali, Amine",31.00,t
C2,"Le ""Grand""",12.50,t
C3,,,t
C4,"",7.00,t
C5,"deux
lignes",1.00,t

D'une requête. COPY (requête) TO STDOUT extrait le résultat d'un SELECT, d'une liste VALUES, ou d'une écriture dotée d'une clause RETURNING :

COPY (SELECT code, upper(libelle) AS lib, prix * 2 AS double
      FROM tarif WHERE prix > 2 ORDER BY code) TO STDOUT;

C1→BENALI, AMINE→62.00
C2→LE "GRAND"→25.00
C4→→14.00
CA-01→CAHIER\t96 PAGES→7.00

COPY (SELECT code, libelle, prix FROM tarif WHERE code LIKE 'C_' ORDER BY code)
TO STDOUT WITH (FORMAT csv, HEADER, DELIMITER ';', NULL 'NUL', QUOTE '''');

code;libelle;prix
C1;Benali, Amine;31.00
C2;Le "Grand";12.50
C3;NUL;NUL
C4;;7.00
C5;'deux
lignes';1.00

COPY (INSERT INTO tarif (code) VALUES ('R1') RETURNING id, code, prix) TO STDOUT WITH CSV HEADER;
-- id,code,prix
-- 14,R1,1.00
COPY (UPDATE tarif SET prix = 9 WHERE code = 'R1' RETURNING code, prix, ttc) TO STDOUT;
-- R1→9.00→10.71
COPY (DELETE FROM tarif WHERE code = 'R1' RETURNING id, code) TO STDOUT;
-- 14→R1
COPY (VALUES (1, 'a'), (2, NULL)) TO STDOUT;
-- 1→a
-- 2→\N

Une écriture sans RETURNING y est refusée (0A000 : COPY query must return rows (SELECT, or a write with RETURNING)).

Une vue s'extrait directement (COPY v_oran TO STDOUT), ce que PostgreSQL n'admet que par COPY (SELECT * FROM v_oran) TO STDOUT. Pour un ordre de sortie garanti, passer par une requête avec ORDER BY.

Extraire dans un fichier du serveur : COPY … TO '/chemin'#

COPY table | (requête) TO 'fichier' écrit les lignes, aux mêmes formats et options que TO STDOUT (en-tête compris), dans un fichier du serveur ; rien n'est envoyé au client, sauf l'étiquette COPY n.

COPY tarif (code, prix) TO '/srv/export/tarif.csv' WITH (FORMAT csv, HEADER);   -- COPY 8
COPY (SELECT code FROM tarif WHERE prix > 10) TO '/srv/export/chers.txt';         -- COPY 2

Contenu de tarif.csv :

code,prix
ST-01,1.20
CA-01,3.50
CL-01,
C1,31.00
C2,12.50
C3,
C4,7.00
C5,1.00

Règles :

• le serveur doit être démarré avec un dossier d'échange --secure-file-priv <dossier> (voir 15.1.3) ; sans lui, l'écriture est refusée à tout compte, root compris (42501 : COPY to a file is disabled: the server has no secure_file_priv directory) ;
• le fichier doit se trouver dans ce dossier, une fois le chemin rendu canonique (.. et liens symboliques résolus) : sinon 42501 (… outside the secure_file_priv directory) ;
• le chemin est absolu (42602 : relative path not allowed for COPY to file) ;
• un fichier existant n'est jamais remplacé (58P02 : could not open file "…" for writing: File exists) ; le fichier est créé avec les droits 0600 (lisible du seul compte du serveur) et retiré si l'instruction échoue ;
• le compte doit avoir le privilège global FILE (GRANT FILE ON *.* TO saisie), équivalent du rôle pg_write_server_files de PostgreSQL ; sans lui :

  ERREUR 42501 : permission denied to COPY to a file
    DÉTAIL : Only roles with privileges of the "pg_write_server_files" role may COPY to a file.
    CONSEIL : Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Droits#

• COPY … FROM STDIN : droit INSERT et droit SELECT sur la table (le serveur lit d'abord ses colonnes) ; sans SELECT, 42501, avant tout envoi de données.
• COPY … TO STDOUT : droit SELECT, comme la requête correspondante.
• COPY … TO 'fichier' : droit SELECT et privilège global FILE.

Non pris en charge#

Toutes ces formes rendent 0A000 :

COPY ne se prépare pas (PREPARE c AS COPY … : 42601) et n'est pas servi par le protocole étendu : les pilotes l'envoient par leur API de copie, qui utilise le protocole simple.

Pour charger un fichier, passer par le poste client (\copy, copy_expert).

34.8 Paramètres et instructions préparées#

Paramètres des pilotes#

Une valeur qui vient de l'application ne se colle pas dans le texte SQL : elle se passe en paramètre. Les pilotes PostgreSQL le font dans leur propre notation, que MIRAJ reçoit comme un serveur PostgreSQL (psycopg2 : %s et %(nom)s). Exemples vérifiés avec psycopg2 :

cur.execute("INSERT INTO client (nom, ville) VALUES (%s, %s) RETURNING id", ("Rachid", "Oran"))
print(cur.fetchone()[0], cur.statusmessage)                 # 21 INSERT 0 1

cur.execute("UPDATE client SET solde = solde + %(m)s WHERE nom = %(n)s", {"m": 12.5, "n": "Rachid"})
print(cur.rowcount)                                         # 1

cur.executemany("INSERT INTO client (nom, ville) VALUES (%s, %s)",
                [("Samia", "Alger"), ("Tarek", None)])

from psycopg2.extras import execute_values
lignes = execute_values(cur, "INSERT INTO client (nom, ville) VALUES %s RETURNING id, nom",
                        [("Yanis", "Oran"), ("Zina", "Blida")], fetch=True)
conn.commit()

cur.rowcount reprend le nombre de l'étiquette ; None en Python devient NULL.

Dans le texte d'une instruction préparée, les paramètres s'écrivent $1, $2… ; un même numéro peut revenir (SELECT $1::int + $1::int).

PREPARE, EXECUTE, DEALLOCATE#

PREPARE nom [(type [, ...])] AS instruction
EXECUTE nom [(valeur [, ...])]
DEALLOCATE [PREPARE] {nom | ALL}

PREPARE ajout_client (text, text) AS
    INSERT INTO client (nom, ville) VALUES ($1, $2) RETURNING id;

EXECUTE ajout_client('Rachid', 'Oran');      -- une ligne : id = 16, puis INSERT 0 1
EXECUTE ajout_client('Samia', NULL);         -- id = 17

PREPARE maj_ville AS UPDATE client SET ville = $2 WHERE nom = $1;
EXECUTE maj_ville('Samia', 'Alger');         -- UPDATE 1

DEALLOCATE ajout_client;
DEALLOCATE ALL;

• Une instruction préparée appartient à la session ; elle disparaît avec elle, ou par DEALLOCATE (DISCARD ALL les libère toutes).
• La liste de types est facultative et sans effet : elle ne contraint pas les paramètres.
• EXECUTE rend l'étiquette de l'instruction exécutée (INSERT 0 1, UPDATE 1) et, avec RETURNING, ses lignes.

34.9 Atomicité, transactions et étiquettes#

• Une instruction est atomique : une erreur à la troisième ligne d'un INSERT, d'un UPDATE ou d'un COPY annule les deux premières.
• Hors transaction (mode autocommit, le défaut de psql), chaque instruction est validée dès qu'elle réussit. Dans un bloc BEGIN … COMMIT, les écritures ne sont visibles des autres sessions qu'au COMMIT, et ROLLBACK les annule — TRUNCATE compris (34.6), à l'exception des numéros serial déjà attribués. Voir 37. Transactions et session, notamment pour les conflits entre transactions qui écrivent la même ligne et les réessais.
• Plusieurs instructions dans un même envoi. Hors transaction, des instructions séparées par ; et envoyées en une seule fois (cur.execute("INSERT …; INSERT …"), psql -c "…; …") forment une seule transaction, comme dans PostgreSQL : si la seconde échoue, la première est annulée aussi (les numéros serial qu'elle a reçus restent consommés).

  INSERT INTO journal (msg) VALUES ('trois'); SELECT 1/0;
  -- INSERT 0 1
  -- ERREUR 22012 : division by zero
  SELECT count(*) AS n FROM journal WHERE msg = 'trois';           -- 0

EXPLAIN UPDATE … et EXPLAIN DELETE … affichent le plan de lecture de MIRAJ (voir 35.14) ; EXPLAIN INSERT … VALUES n'est pas pris en charge (0A000).

34.10 Extensions MIRAJ acceptées#

Ces formes ne sont pas du PostgreSQL ; elles viennent du langage MIRAJ et restent acceptées, écrites avec la lexicographie de PostgreSQL. Elles sont décrites au chapitre 7. Un script qui doit aussi s'exécuter sur un serveur PostgreSQL ne doit pas les employer.

Pour un upsert, préférer ON CONFLICT (34.5), qui est la forme de PostgreSQL et dont le compte de lignes suit PostgreSQL.

34.11 Non pris en charge et écarts#

Formes refusées#

Écarts de comportement#

La liste condensée de ces points est tenue au chapitre 29 (section 29.11).

34.12 Récapitulatif des SQLSTATE des écritures#