32. PostgreSQL : Prise en main

miraj-server.exe --root D:\donnees --log --sql-mode "STRICT_TRANS_TABLES,POSTGRESQL"

MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, langage PostgreSQL, dossier "D:\donnees", 12 fils, licence ENT-0001 (Exemple SARL).
Configuration : D:\donnees\miraj_config.xml.

$env:PGPASSWORD = "mot de passe"
psql -h 127.0.0.1 -p 7007 -U root -d ventes
psql "host=127.0.0.1 port=7007 user=root dbname=ventes"

SELECT version();

                                 version()
----------------------------------------------------------------------------
 PostgreSQL 16.4 (MIRAJ 1.0.0) on x86_64-windows, 64-bit
(1 ligne)

SELECT current_database(), current_schema, current_user;

 current_database() | current_schema | current_user
--------------------+----------------+--------------
 miraj              | ventes         | root
(1 ligne)

miraj-cli.exe --port 7007 --user root --password -d ventes

MIRAJ 1.0.0 - connecté (langage PostgreSQL) - tapez \help pour l'aide, \q pour quitter.
ventes=>

ventes=> SELECT code, prix FROM article ORDER BY id LIMIT 2;
+--------+------+
| code   | prix |
+--------+------+
| STY-01 | 1.30 |
| CAH-96 | 2.50 |
+--------+------+
2 ligne(s)
ventes=> SELECT 1/0;
ERREUR 22012 : division by zero

import psycopg2

conn = psycopg2.connect(host="127.0.0.1", port=7007, user="root",
                        password="mot de passe", dbname="ventes")
cur = conn.cursor()
cur.execute("SELECT id, nom FROM client WHERE ville = %s", ("Alger",))
print(cur.fetchall())          # [(1, 'Amel Kaci')]
print(cur.statusmessage)       # SELECT 1

cur.execute("INSERT INTO client (nom, email, ville) VALUES (%s, %s, %s) RETURNING id",
            ("Nadia Haddad", "[email protected]", "Oran"))
print(cur.fetchone()[0])       # 4 : identifiant généré
conn.commit()
conn.close()

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE SCHEMA
SET

CREATE TABLE client (
    id       serial PRIMARY KEY,
    nom      varchar(80) NOT NULL,
    email    varchar(120) NOT NULL UNIQUE,
    ville    text,
    actif    boolean NOT NULL DEFAULT true,
    cree_le  timestamp 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
);

CREATE TABLE commande (
    id          serial PRIMARY KEY,
    client_id   integer NOT NULL REFERENCES client (id),
    passee_le   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,
    PRIMARY KEY (commande_id, article_id)
);

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

INSERT INTO client (nom, email, ville) VALUES
    ('Amel Kaci', '[email protected]', 'Alger'),
    ('Youssef Ben Ali', '[email protected]', 'Constantine'),
    ('Claire Dupont', '[email protected]', 'Lyon');

INSERT 0 3

INSERT INTO article (code, libelle, prix, stock) VALUES
    ('STY-01', 'Stylo bille', 1.20, 500),
    ('CAH-96', 'Cahier 96 pages', 2.50, 200),
    ('CLA-A4', 'Classeur A4', 4.90, 80)
RETURNING id, code;

 id |  code
----+--------
  1 | STY-01
  2 | CAH-96
  3 | CLA-A4
(3 lignes)

INSERT 0 3

INSERT INTO commande (client_id, passee_le) VALUES
    (1, '2026-10-01'), (1, '2026-10-05'), (2, '2026-10-06');
INSERT INTO ligne_commande VALUES
    (1, 1, 10, 1.20), (1, 2, 4, 2.50), (2, 3, 2, 4.90), (3, 2, 20, 2.30);

INSERT 0 3
INSERT 0 4

INSERT INTO client (nom, email) VALUES ('Doublon', '[email protected]');
INSERT INTO article (code, libelle, prix) VALUES ('X', 'Prix négatif', -1);
INSERT INTO commande (client_id) VALUES (99);

ERREUR 23505 : Duplicate entry '[email protected]' for key 'email'
ERREUR 23514 : new row for relation "article" violates check constraint "CONSTRAINT_1"
ERREUR 23503 : Cannot add or update a child row: a foreign key constraint fails ("ventes"."commande", CONSTRAINT "commande_ibfk_1" FOREIGN KEY ("client_id") REFERENCES "client" ("id"))

UPDATE article SET stock = stock - 10 WHERE code = 'STY-01' RETURNING code, stock;

  code  | stock
--------+-------
 STY-01 |   490
(1 ligne)

UPDATE 1

INSERT INTO article (code, libelle, prix, stock) VALUES ('STY-01', 'Stylo bille bleu', 1.30, 100)
ON CONFLICT (code) DO UPDATE SET prix = EXCLUDED.prix, stock = article.stock + EXCLUDED.stock
RETURNING code, prix, stock;

  code  | prix | stock
--------+------+-------
 STY-01 | 1.30 |   590
(1 ligne)

INSERT 0 1

BEGIN;
DELETE FROM client WHERE id = 3 RETURNING nom;
UPDATE article SET stock = 0;
ROLLBACK;
SELECT code, stock FROM article ORDER BY id;

BEGIN
      nom
---------------
 Claire Dupont
(1 ligne)

DELETE 1
UPDATE 3
ROLLBACK
  code  | stock
--------+-------
 STY-01 |   590
 CAH-96 |   200
 CLA-A4 |    80
(3 lignes)

ALTER USER root@localhost IDENTIFIED BY 'nouveau mot de passe';

CREATE USER app IDENTIFIED BY 'secret123';
GRANT SELECT, INSERT, UPDATE, DELETE ON ventes.* TO app;

CREATE USER
GRANT

CREATE USER app2 WITH PASSWORD 'secret123';
CREATE ROLE app3 LOGIN PASSWORD 'secret123';

CREATE USER
CREATE ROLE

SELECT id, nom, email FROM client WHERE nom ILIKE '%a%' ORDER BY nom;

 id |       nom       |           email
----+-----------------+----------------------------
  1 | Amel Kaci       | [email protected]
  3 | Claire Dupont   | [email protected]
  2 | Youssef Ben Ali | [email protected]
(3 lignes)

SELECT id, nom, actif, cree_le::date AS jour FROM client ORDER BY id;

 id |       nom       | actif |    jour
----+-----------------+-------+------------
  1 | Amel Kaci       | t     | 2026-10-11
  2 | Youssef Ben Ali | t     | 2026-10-11
  3 | Claire Dupont   | t     | 2026-10-11
(3 lignes)

SELECT c.nom, o.id AS commande, o.passee_le, sum(l.quantite * l.prix_unit) AS montant
FROM commande o
JOIN client c ON c.id = o.client_id
JOIN ligne_commande l ON l.commande_id = o.id
GROUP BY c.nom, o.id, o.passee_le
ORDER BY montant DESC;

       nom       | commande | passee_le  | montant
-----------------+----------+------------+---------
 Youssef Ben Ali |        3 | 2026-10-06 |   46.00
 Amel Kaci       |        1 | 2026-10-01 |   22.00
 Amel Kaci       |        2 | 2026-10-05 |    9.80
(3 lignes)

SELECT c.nom, count(DISTINCT o.id) AS nb_commandes, sum(l.quantite * l.prix_unit) AS total
FROM client c
LEFT JOIN commande o ON o.client_id = c.id
LEFT JOIN ligne_commande l ON l.commande_id = o.id
GROUP BY c.id, c.nom
ORDER BY total DESC NULLS LAST;

       nom       | nb_commandes | total
-----------------+--------------+-------
 Youssef Ben Ali |            1 | 46.00
 Amel Kaci       |            2 | 31.80
 Claire Dupont   |            0 |
(3 lignes)

CREATE INDEX idx_commande_date ON commande (passee_le);

CREATE INDEX

SELECT tablename FROM pg_tables WHERE schemaname = 'ventes' ORDER BY tablename;

   tablename
----------------
 article
 client
 commande
 ligne_commande
(4 lignes)

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'ventes' AND table_name = 'client'
ORDER BY ordinal_position;

 column_name |          data_type          | is_nullable |           column_default
-------------+-----------------------------+-------------+------------------------------------
 id          | integer                     | NO          | nextval('client_id_seq'::regclass)
 nom         | character varying           | NO          |
 email       | character varying           | NO          |
 ville       | text                        | YES         |
 actif       | boolean                     | NO          | true
 cree_le     | timestamp without time zone | YES         | now()
(6 lignes)

SHOW CREATE TABLE commande;

CREATE TABLE "commande" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY (START WITH 4) NOT NULL,
  "client_id" integer NOT NULL,
  "passee_le" date NOT NULL DEFAULT CURRENT_DATE,
  "etat" character varying(12) NOT NULL DEFAULT 'ouverte'::character varying,
  PRIMARY KEY ("id"),
  CONSTRAINT "commande_ibfk_1" FOREIGN KEY ("client_id") REFERENCES "client" ("id")
);
CREATE INDEX "idx_commande_date" ON "commande" ("passee_le")

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'commande' ORDER BY indexname;

     indexname     |                                 indexdef
-------------------+---------------------------------------------------------------------------
 commande_pkey     | CREATE UNIQUE INDEX commande_pkey ON ventes.commande USING btree (id)
 idx_commande_date | CREATE INDEX idx_commande_date ON ventes.commande USING btree (passee_le)
(2 lignes)

SELECT `nom` FROM client;
SELECT * FROM client LIMIT 1, 2;

ERREUR 42601 : syntax error at or near "`" at line 1: a backquoted identifier is MIRAJ syntax, not accepted in the PostgreSQL language
ERREUR 42601 : syntax error at or near "," at line 1: LIMIT offset, count is MIRAJ syntax, not accepted in the PostgreSQL language