35. PostgreSQL: DML language

This chapter describes the statements that write data to a MIRAJ instance in the PostgreSQL language: INSERT, UPDATE, DELETE, the RETURNING clause, INSERT … ON CONFLICT, TRUNCATE, COPY and prepared statements (PREPARE / EXECUTE). It describes the PostgreSQL language as MIRAJ speaks it: all examples were run on a real instance and the results copied; whatever differs from PostgreSQL is pointed out in the text and collected in 35.11.

The examples rely on the database (schema) ventes and its four 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)
);

Table definition is described in chapter 34. Data definition language (DDL), types and value syntax in chapter 33. Data types, queries in chapter 36. SELECT queries.

Each write returns its command tag to the client, like PostgreSQL: INSERT 0 n, UPDATE n, DELETE n, COPY n, TRUNCATE TABLE. The results in this chapter are shown in the style of psql (NULL is spelled out for readability).

35.1 INSERT#

Synopsis#

INSERT INTO [schema.]table [AS alias] [(column [, ...])]
    [OVERRIDING {SYSTEM | USER} VALUE]
    { VALUES ({expr | DEFAULT} [, ...]) [, (...) ...]
    | DEFAULT VALUES
    | query
    | (query) }
    [ON CONFLICT [target] {DO NOTHING | DO UPDATE SET ... [WHERE condition]}]   -- 4.5
    [RETURNING item [, ...]]                                                    -- 4.4

query is a SELECT (optionally preceded by WITH) or a VALUES list.

One row, several rows#

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

The sample tables are filled as follows for the rest of the chapter:

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);

• The table name can be qualified by its schema (ventes.client); public.client designates the table of the current database.
• The column list is optional. Without a list, the values go to the columns in the order of their definition (a shorter list fills the first columns, see below).
• A column missing from the list receives its default value (the DEFAULT of the definition, the next value of a serial or identity column, NULL otherwise).

DEFAULT and DEFAULT VALUES#

The word DEFAULT in place of a value requests the column's default value; DEFAULT VALUES inserts a row in which every column takes its default value.

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 fails if a NOT NULL column has no default value:

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

INSERT … SELECT#

The inserted rows come from the result of a query:

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

The query can be written in parentheses, start with WITH and read the table it writes to: it then sees the state from before the statement.

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: client 3 (Chloé)

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

The WITH clause is written in front of the SELECT, after INSERT INTO table, or at the head of the statement, as in PostgreSQL; its table expressions then apply to the whole write, and likewise in front of UPDATE and 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);

A WITH expression can itself write (data-modifying table expression): WITH name AS (INSERT | UPDATE | DELETE … RETURNING …) in front of a SELECT, an INSERT, an UPDATE or a DELETE. The write runs only once, even if referenced several times or never; its name designates the rows of its RETURNING (without RETURNING, it runs but cannot be read).

-- Job queue: take rows and remove them in one move
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)

-- Move rows to an 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;

Outside a transaction block, the whole statement is atomic: failure of the main statement cancels the writes. Limits: a write is allowed only in the WITH clause that opens the statement (not in a subquery, a view, CREATE TABLE … AS, EXPLAIN, nor in front of SELECT … INTO: 0A000); the writes run in the order written, before the main statement, which therefore sees the tables as they left them (PostgreSQL shows it their state from before the statement).

A VALUES list without a column list can give fewer values than the table has columns: they go to the first columns, and the following ones take their default value (INSERT INTO gros_client VALUES (51) leaves total at its default value). The same applies to INSERT INTO t SELECT …. More values than columns, rows of different lengths, or a number of values that does not match a written column list return 42601.

INSERT INTO table AS alias gives an alias to the target table; it is used in ON CONFLICT … DO UPDATE … WHERE alias.column … (34.5) and in RETURNING, and is still accepted without them.

With a serial column, numbers are assigned in the order of the query rows (ORDER BY included).

serial and identity columns#

A serial, bigserial or GENERATED … AS IDENTITY column receives the next number when the statement gives it no value, gives it DEFAULT, or gives it NULL:

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

Good to know:

• a written value does not advance the counter, like a PostgreSQL sequence: after inserting id = 20, the next row receives 6. The generated numbers eventually meet the written value: the insert that receives 20 fails (23505, Duplicate entry '20' for key 'PRIMARY'). Like a nextval, this number is consumed despite the failure (in a transaction too, even if rolled back afterwards): the next insert receives 21 and succeeds. A number drawn by a cancelled insert (ROLLBACK) is not reused either;
• NULL written into a serial or GENERATED BY DEFAULT AS IDENTITY column requests the next number (PostgreSQL: error 23502);
• GENERATED … AS IDENTITY (START WITH n) sets the first number:

  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

• a GENERATED ALWAYS AS IDENTITY column refuses a written value, NULL included (428C9), as in PostgreSQL; only DEFAULT is accepted there, except under 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');
  -- ERROR 428C9: cannot insert a non-DEFAULT value into column "id"
  --   DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
  --   HINT: 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';
  -- ERROR 428C9: column "id" can only be updated to DEFAULT
  --   DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
  UPDATE ticket SET id = DEFAULT WHERE objet = 'a' RETURNING id;        -- next number (4)

  OVERRIDING SYSTEM VALUE keeps the written value; OVERRIDING USER VALUE ignores it and assigns the next number for an identity column, but keeps it in a serial column, which is not one (as in PostgreSQL). COPY … FROM STDIN writes the values of the file, as under OVERRIDING SYSTEM VALUE. miraj-dump writes the rows of such a table as INSERT … OVERRIDING SYSTEM VALUE. The clause also applies in the body of a PL/pgSQL or SQL routine. On the PostgreSQL protocol, the refusal carries the detail and the hint (Use OVERRIDING SYSTEM VALUE to override.) in their fields, as PostgreSQL does;

• an assigned number is never given back: a statement cancelled by ROLLBACK leaves a gap in the numbering. A statement that fails (duplicate, constraint) also consumes the numbers it drew, like nextval in PostgreSQL; the 428C9 refusal of a value written into a GENERATED ALWAYS column, raised before any draw, consumes none;
• the assigned number is read with RETURNING (35.4), or with lastval(), which returns the last value served to the session by a serial / IDENTITY column or by nextval (55000 as long as the session has received none). There is no client_id_seq sequence behind a serial column: currval('client_id_seq') returns 42P01.

Generated columns#

A GENERATED ALWAYS AS (expr) STORED column is computed by MIRAJ. It is omitted, or receives DEFAULT; a value written for it is refused, as in PostgreSQL:

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit, total)
VALUES (3, 2, 1, 3.50, 999);
-- ERROR 428C9: cannot insert a non-DEFAULT value into column "total"
--   DETAIL: 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

Values drawn from a sequence#

A sequence (see 34. Data definition language (DDL)) supplies a value through the column's DEFAULT or directly in the statement:

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

Writing values#

Values are written with PostgreSQL lexical syntax: standard string 'l''été' (the backslash is an ordinary character there), escape string E'a\tb', dollar-quoted string $$l'été "x"$$. A value is converted to the column's type:

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

• a decimal is rounded to the column's scale (0.999 → 1.00), and a decimal written into an integer column is rounded (2.7 → 3);
• a text is read according to the column's type ('12' into an integer, '2026-09-01' into a date); a boolean column reads 't', 'true', 'yes', 'on', '1' and their opposites;
• a value that cannot be converted, is too long or is outside the type's bounds is an error: nothing is silently truncated (the example instance is started with STRICT_TRANS_TABLES, see 32. Getting started).

Constraints#

Constraints are checked when each row is written. A statement is all or nothing: the first error cancels all its rows.

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

Both 23502 refusals apply whatever sql_mode is, even without STRICT_TRANS_TABLES, for INSERT, UPDATE and ON CONFLICT … DO UPDATE: in the PostgreSQL language, a NULL written into a NOT NULL column is never replaced by the type's implicit value.

The SQLSTATE codes are those of PostgreSQL. The messages name objects between double quotes and speak of schema, relation and role; the most common ones reuse PostgreSQL's text (relation "…" does not exist, new row for relation "…" violates check constraint "…"), the others keep MIRAJ's (in English). An application should therefore rely on the SQLSTATE, not on the text.

Uniqueness and case. Texts are compared byte by byte ("C" collation, like PostgreSQL), including in uniqueness constraints: '[email protected]' is not a duplicate of '[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

35.2 UPDATE#

Synopsis#

UPDATE [schema.]table [[AS] alias]
    SET { column = {expr | DEFAULT}
        | (column [, ...]) = [ROW] ({expr | DEFAULT} [, ...]) } [, ...]
    [FROM source [, ...]]
    [WHERE condition]
    [RETURNING item [, ...]]                                                   -- 4.4

Simple form#

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

The number in the tag is that of the rows selected by WHERE, whether or not their value changes, like PostgreSQL. Without WHERE, all rows are modified.

The table can carry an alias; a column to the left of the = can be qualified by the table or by the 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;     -- default value, NULL

Assigning several columns#

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

The right-hand list is a list of values; a subquery in that position (SET (a, b) = (SELECT …)) is not supported (0A000).

Expressions read the old values#

As in PostgreSQL, all SET expressions are computed on the row from before the modification, whatever their order:

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;         -- swaps the two columns
UPDATE paire SET a = 100, b = a + 1 WHERE id = 2;   -- b = old a + 1
SELECT * FROM paire ORDER BY id;

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

Subqueries#

A subquery can supply a value (it can be correlated to the modified row) or designate the rows to modify. If it reads the modified table, it sees the state from before the statement.

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);

A WITH expression is written in the subquery (WHERE id IN (WITH x AS (…) SELECT id FROM x)) or in front of the word UPDATE (WITH x AS (…) UPDATE …, see 35.1).

UPDATE ONLY table, DELETE FROM ONLY table and table * are accepted: ONLY and * have no effect, since MIRAJ has no table inheritance.

UPDATE … FROM: values taken from other tables#

The FROM clause joins the modified table to other sources; the join condition is written in WHERE. Only the table that follows UPDATE is modified.

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

The sources can be several tables separated by commas, explicit joins or a derived table:

-- derived table: total of order lines per customer
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

-- explicit join in 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

-- table list
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;

A row of the modified table matched with several rows of the source is modified and counted only once (UPDATE paire … FROM (VALUES (1), (1), (2)) AS v (x) WHERE v.x = paire.id: UPDATE 2); the source row retained is not defined, as in PostgreSQL.

A table can be joined to itself under a second alias (UPDATE t SET b = t2.b FROM t t2 WHERE t2.a = t.a + 1).

Generated columns, constraints#

A generated column cannot be assigned: only DEFAULT is accepted there. It is recomputed when its operands change.

UPDATE ligne_commande SET total = 5 WHERE commande_id = 1;
-- ERROR 428C9: column "total" can only be updated to DEFAULT
--   DETAIL: 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

Constraints are checked as for an INSERT, and the first error cancels the whole statement:

35.3 DELETE#

Synopsis#

DELETE FROM [schema.]table [[AS] alias]
    [USING source [, ...]]
    [WHERE condition]
    [RETURNING item [, ...]]                                                   -- 4.4

Simple form#

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

Subqueries are used as in an UPDATE, correlated or not:

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;                        -- all rows: DELETE 2

DELETE FROM table without a condition keeps the counter of a serial column, unlike TRUNCATE (35.6):

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

DELETE … USING: deleting based on other tables#

USING joins the table being emptied to other sources, the join condition being written in WHERE. Only the table that follows DELETE FROM loses rows.

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

-- the "article 4" lines of customer 3's orders
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

-- several sources, as a list or an explicit join
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';

The table can be joined to itself (DELETE FROM t USING t t2 WHERE t2.a = t.a + 1).

Foreign keys#

The ON DELETE actions of foreign keys apply. A row that is still referenced is protected (23503); ON DELETE CASCADE deletes the child rows, without counting them in the tag:

DELETE FROM client WHERE id = 1;
-- ERROR 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: its order lines go with it

Constraints are checked immediately, row by row (DEFERRABLE and INITIALLY DEFERRED are accepted at creation, with no effect).

35.4 RETURNING#

INSERT, UPDATE and DELETE accept a RETURNING clause, written like the list of a SELECT: *, table.*, columns (qualified or not by the table or its alias), expressions, aliases. The statement returns one row per row written, in the order of the writes, then its usual tag.

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

Returned values#

• INSERT and UPDATE return the values after the write: number of a serial or identity column, default values, generated columns, values converted to the column's type.
• DELETE returns the values from before the deletion.
• With ON CONFLICT (35.5), only the rows inserted or modified are returned; a row discarded by DO NOTHING or by a false WHERE is not.
• A statement that writes no row returns an empty result, with its columns.

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

Rules#

• Column names: an expression without an alias carries the text as written (RETURNING id + 1, upper(nom) names its columns id + 1 and upper(nom); PostgreSQL would name them ?column? and upper). Give an alias to every expression read by name.
• Check before writing: an unknown column in the clause is refused (42703, Unknown column 'inconnue' in 'returning list') without anything being written.
• Subquery: an uncorrelated subquery is allowed; it sees the state from before the statement, like PostgreSQL.

  SELECT count(*) AS n FROM client;                                          -- 12
  INSERT INTO client (nom) VALUES ('P') RETURNING (SELECT count(*) FROM client) AS avant;
  -- 12: the inserted row is not counted

• Aggregate in the clause (RETURNING count(*)): 42803.
• Privileges: in addition to the right to write, the account must have the SELECT privilege on the table; otherwise 42501 (permission denied for table …). See 39. Accounts and privileges.
• Drivers: the rows of the clause are read like those of a SELECT (psycopg2's cursor.fetchone() after an INSERT … RETURNING).
• In a transaction, a ROLLBACK cancels the rows already returned to the client.

RETURNING is the way to read an assigned serial number:

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

Not supported#

35.5 INSERT … ON CONFLICT#

ON CONFLICT decides the fate of a proposed row that hits a uniqueness constraint: discard it (DO NOTHING) or modify the existing row (DO UPDATE, the "upsert").

Synopsis#

INSERT INTO table [AS alias] [(column [, ...])] {VALUES ... | query}
    ON CONFLICT [ (column [, ...]) | ON CONSTRAINT constraint_name ]
    { DO NOTHING
    | DO UPDATE SET { column = {expr | DEFAULT}
                    | (column [, ...]) = ({expr | DEFAULT} [, ...]) } [, ...]
                [WHERE condition] }
    [RETURNING item [, ...]]

The examples use a counter table:

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

The conflicting row is discarded without error or notice; the other rows of the statement are inserted. Two proposed rows with the same key in the same statement: the first is inserted, the second discarded.

DO UPDATE#

In SET and WHERE:

• a column alone, or qualified by the table name or by its alias (INSERT INTO t AS x), designates the existing row;
• EXCLUDED.column designates the proposed row (default values included);
• DEFAULT resets the column's default value.

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 existed (1 + 5 = 6), c was inserted. The three ways of writing the name of the existing row are equivalent:

... DO UPDATE SET n = x.n + EXCLUDED.n          -- alias of INSERT INTO compteur AS x
... DO UPDATE SET n = compteur.n + EXCLUDED.n   -- table name
... DO UPDATE SET n = n + EXCLUDED.n            -- column alone

WHERE leaves the existing row as it is when the condition is not true; the row is then neither counted nor returned by RETURNING:

-- compteur contains 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

Other verified forms:

-- several columns at once
INSERT INTO compteur (code, n) VALUES ('c', 5)
ON CONFLICT (code) DO UPDATE SET (n, maj) = (EXCLUDED.n, NULL);

-- default value
INSERT INTO compteur (code, n) VALUES ('c', 5) ON CONFLICT (code) DO UPDATE SET n = DEFAULT;

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

-- multi-column key; the generated column is recomputed
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

The target: which constraint arbitrates#

Constraint names. The primary key is named <table>_pkey, as in PostgreSQL. An unnamed UNIQUE constraint declared on a column carries, in MIRAJ, the name of that column (lib), not <table>_<column>_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;
-- ERROR 42704: constraint "compteur_lib_key" for table "compteur" does not exist

For a stable name, name the constraint at creation (CONSTRAINT compteur_lib_key UNIQUE (lib)) or designate the target by its columns. The actual names can be read in pg_constraint (see 41. Catalog and tools).

Only the arbiter is handled. A row that hits a uniqueness constraint other than the target receives the usual error, as in PostgreSQL:

-- 'z' is free, but lib = 'premier' already exists
INSERT INTO compteur (code, lib) VALUES ('z', 'premier') ON CONFLICT (code) DO NOTHING;
-- ERROR 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

Rules#

• Count: the INSERT 0 n tag counts the rows inserted and the rows modified; a conflicting row modified without a change of value is also counted. DO NOTHING and a false WHERE are not counted.
• A row cannot be touched twice by the same DO UPDATE statement: two proposed rows with the same key give 21000 and nothing is written.

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

• DO UPDATE requires a target: ON CONFLICT DO UPDATE … returns 42601 (ON CONFLICT DO UPDATE requires inference specification or constraint name).
• Other errors remain errors: NOT NULL (23502), CHECK, foreign key, conversion.
• Case: text keys are compared byte by byte; 'A' does not conflict with 'a' and is inserted as a new row (INSERT INTO compteur (code, n) VALUES ('A', 7) ON CONFLICT (code) DO UPDATE SET n = EXCLUDED.n → INSERT 0 1, row A added).
• Privileges: INSERT, and UPDATE for DO UPDATE (42501 otherwise, even when no row is in conflict).

Not supported#

35.6 TRUNCATE#

TRUNCATE [TABLE] [ONLY] [schema.]table
    [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]

TRUNCATE empties a table in a single block, without going row by row (DELETE triggers do not run). The tag is TRUNCATE TABLE.

TRUNCATE journal;
INSERT INTO journal (msg) VALUES ('après TRUNCATE') RETURNING id;      -- 5: the counter continues

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

Good to know when coming from PostgreSQL:

• Identity counter: as in PostgreSQL, it continues (CONTINUE IDENTITY, the default) and restarts at 1 with RESTART IDENTITY, outside a block as in a block. In a block, a ROLLBACK (or ROLLBACK TO SAVEPOINT) gives it back its previous value.
• One table at a time: TRUNCATE journal, compteur returns 0A000 (TRUNCATE of several tables is not supported yet in the PostgreSQL language).

• In a transaction block, TRUNCATE is transactional, as in PostgreSQL: ROLLBACK and ROLLBACK TO SAVEPOINT give the rows back, and other sessions see the table empty only at COMMIT.

  BEGIN;
  TRUNCATE journal;
  SELECT count(*) AS n FROM journal;      -- 0
  ROLLBACK;
  SELECT count(*) AS n FROM journal;      -- the rows are back

  As in PostgreSQL, TRUNCATE locks the table (and, with CASCADE, the ones it also empties) in ACCESS EXCLUSIVE mode until the end of the transaction: it first waits for the other transactions that hold rows in it (or that have read it, in the default concurrency model), then any other session that reads or writes the table waits for the COMMIT or the ROLLBACK, for at most lock_timeout, then 55P03 (38.6). Two transactions waiting for each other: 40P01 for one of them.

  The table is emptied in a single block there, as outside a block, in a time that does not depend on the number of rows (without triggers or foreign key actions; a versioned table also loses its history): its content is set aside until the end of the transaction, given back by ROLLBACK or by a ROLLBACK TO SAVEPOINT to a point set before the TRUNCATE, and released at COMMIT. Writes made by the transaction before the TRUNCATE disappear with it, those made after it stay; two consecutive TRUNCATE statements and unique keys reinserted after it behave as in PostgreSQL. An on-disk table (ENGINE = disk) is also emptied as a block: its file stays intact until COMMIT, and the new content is written to a new file at the next save point; a partitioned table of the Cluster edition sees each of its segments emptied as a block. Only a partitioned table with a partition held by another node still has its rows deleted one by one under the transaction.

• Privileges: TRUNCATE requires the DROP privilege on the table (42501: permission denied for table …); there is no separate TRUNCATE privilege.

Referenced tables#

A table referenced by a foreign key cannot be emptied alone; CASCADE also empties, step by step, the tables that reference it:

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;              -- or TRUNCATE parent RESTRICT
-- ERROR 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 and enfant are empty

35.7 COPY#

COPY loads or extracts rows in bulk, in text or CSV format. Loading goes through the client connection (FROM STDIN); extraction goes through the connection (TO STDOUT) or is written to a file on the server (TO '/path', see below).

Synopsis#

COPY table [(column [, ...])] FROM STDIN [[WITH] (option [, ...])]
COPY table [(column [, ...])] TO {STDOUT | 'file'} [[WITH] (option [, ...])]
COPY (query) TO {STDOUT | 'file'} [[WITH] (option [, ...])]

The old form of the options is also accepted: WITH DELIMITER AS '|' NULL AS '', WITH CSV HEADER, CSV.

On the client side, COPY is used through \copy in psql (which reads or writes a file on the client machine: \copy tarif from 'tarif.csv' with (format csv, header)), and through psycopg2's copy_expert, copy_from and copy_to:

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)        # number of rows loaded

The examples use this 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
);

Loading: COPY … FROM STDIN#

Text format. One record per line, fields separated by a tab (written → below), NULL written \N, escapes \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. In psql, the \. line ends the data; a driver ends it by closing the stream.

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

(the label of row 2 contains a real tab, shown here as \t.)

• Columns missing from the list: they receive their default value (serial number, DEFAULT), as in an INSERT. An explicit \N, on the other hand, writes NULL, even if the column has a default value (prix of row 3).
• Without a column list, the data carries all the table's columns in their order, except generated columns (here six fields, ttc excluded). A value given to the serial column is kept, without advancing its counter.
• A generated column named in the list is refused (428C9: cannot insert a non-DEFAULT value into column "ttc"), as in an INSERT.

CSV format. Fields separated by a comma, quoted with double quotes when they contain the separator, a quote (doubled: "") or a line break. An unquoted empty field is NULL; "" is the empty string.

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

Format options, verified:

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

Values read. Conversions are those of an INSERT:

• booleans written as in PostgreSQL (t, true, yes, on, 1, f, no…);
• bytea in hexadecimal (\\x00ff in text format, where the backslash is doubled);
• a uuid is kept in its canonical lowercase form;
• a date-time written with an offset (2026-10-06 10:00:00+02) loses that offset in a timestamp column (2026-10-06 10:00:00) and designates its exact instant in a timestamptz column (read back as 2026-10-06 09:00:00+01 in a UTC+1 session).

All or nothing. A COPY … FROM STDIN is a single statement: it runs like an INSERT (default values, constraints, foreign keys, triggers) and an error, wherever it is in the data, writes no row. In a transaction block, it is cancelled by ROLLBACK. The server keeps the data in memory until the end of the transfer, then loads it: plan for this memory for a very large load, or split it.

The row number is given only for format errors (22P04, boolean); conversion and constraint errors cite the column.

Extracting: 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. Without a column list, generated columns are not written (ttc is absent): the result can be reloaded as is with COPY tarif FROM STDIN. The values are those a SELECT returns as text (t / f, ISO dates, bytea as \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

From a query. COPY (query) TO STDOUT extracts the result of a SELECT, a VALUES list, or a write with a RETURNING clause:

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

A write without RETURNING is refused there (0A000: COPY query must return rows (SELECT, or a write with RETURNING)).

A view can be extracted directly (COPY v_oran TO STDOUT), which PostgreSQL accepts only through COPY (SELECT * FROM v_oran) TO STDOUT. For a guaranteed output order, use a query with ORDER BY.

Extracting to a server file: COPY … TO '/path'#

COPY table | (query) TO 'file' writes the rows, in the same formats and options as TO STDOUT (header included), to a file on the server; nothing is sent to the client except the COPY n tag.

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

Content of 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

Rules:

• the server must be started with an exchange folder --secure-file-priv <folder> (see 16.1.3); without it, writing is refused to every account, root included (42501: COPY to a file is disabled: the server has no secure_file_priv directory);
• the file must be located in this folder, once the path has been made canonical (.. and symbolic links resolved): otherwise 42501 (… outside the secure_file_priv directory);
• the path is absolute (42602: relative path not allowed for COPY to file);
• an existing file is never replaced (58P02: could not open file "…" for writing: File exists); the file is created with 0600 permissions (readable only by the server's account) and removed if the statement fails;
• the account must have the global FILE privilege (GRANT FILE ON *.* TO saisie), the equivalent of the PostgreSQL pg_write_server_files role; without it:

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

Privileges#

• COPY … FROM STDIN: INSERT privilege and SELECT privilege on the table (the server first reads its columns); without SELECT, 42501, before any data is sent.
• COPY … TO STDOUT: SELECT privilege, like the corresponding query.
• COPY … TO 'file': SELECT privilege and global FILE privilege.

Not supported#

All these forms return 0A000:

COPY cannot be prepared (PREPARE c AS COPY …: 42601) and is not served by the extended protocol: drivers send it through their copy API, which uses the simple protocol.

To load a file, go through the client machine (\copy, copy_expert).

35.8 Parameters and prepared statements#

Driver parameters#

A value that comes from the application is not pasted into the SQL text: it is passed as a parameter. PostgreSQL drivers do this in their own notation, which MIRAJ receives like a PostgreSQL server (psycopg2: %s and %(name)s). Examples verified with 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 takes the number from the tag; None in Python becomes NULL.

In the text of a prepared statement, parameters are written $1, $2…; the same number can appear again (SELECT $1::int + $1::int).

PREPARE, EXECUTE, DEALLOCATE#

PREPARE name [(type [, ...])] AS statement
EXECUTE name [(value [, ...])]
DEALLOCATE [PREPARE] {name | ALL}

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

EXECUTE ajout_client('Rachid', 'Oran');      -- one row: id = 16, then 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;

• A prepared statement belongs to the session; it disappears with it, or through DEALLOCATE (DISCARD ALL releases them all).
• The type list is optional and has no effect: it does not constrain the parameters.
• EXECUTE returns the tag of the statement executed (INSERT 0 1, UPDATE 1) and, with RETURNING, its rows.

35.9 Atomicity, transactions and tags#

• A statement is atomic: an error on the third row of an INSERT, an UPDATE or a COPY cancels the first two.
• Outside a transaction (autocommit mode, the psql default), each statement is committed as soon as it succeeds. In a BEGIN … COMMIT block, writes are visible to other sessions only at COMMIT, and ROLLBACK cancels them, TRUNCATE included (35.6), except for serial numbers already assigned. See 38. Transactions and session, in particular for conflicts between transactions that write the same row and retries.
• Several statements in a single send. Outside a transaction, statements separated by ; and sent all at once (cur.execute("INSERT …; INSERT …"), psql -c "…; …") form a single transaction, as in PostgreSQL: if the second fails, the first is cancelled too (the serial numbers it received stay consumed).

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

EXPLAIN UPDATE … and EXPLAIN DELETE … display MIRAJ's read plan (see 36.14); EXPLAIN INSERT … VALUES is not supported (0A000).

35.10 Accepted MIRAJ extensions#

These forms are not PostgreSQL; they come from the MIRAJ language and remain accepted, written with PostgreSQL lexical syntax. They are described in chapter 8. A script that must also run on a PostgreSQL server must not use them.

For an upsert, prefer ON CONFLICT (35.5), which is the PostgreSQL form and whose row count follows PostgreSQL.

35.11 Unsupported features and differences#

Refused forms#

Behavioral differences#

The condensed list of these points is kept in chapter 30 (section 30.11).

35.12 Summary of write SQLSTATE codes#