40. PostgreSQL: Catalog and tools

SELECT oid, nspname FROM pg_namespace ORDER BY nspname;

    oid     |      nspname
------------+--------------------
      13183 | information_schema
  689122271 | miraj
         11 | pg_catalog
 1505511691 | ventes
(4 lignes)

SELECT current_database(), current_schema, current_schemas(true), current_schemas(false);

 current_database() | current_schema | current_schemas(true) | current_schemas(false)
--------------------+----------------+-----------------------+------------------------
 miraj              | ventes         | {pg_catalog,ventes}   | {ventes}
(1 ligne)

CREATE VIEW v_tables AS
SELECT n.nspname AS schema, c.relname AS nom
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'ventes';

INSERT INTO pg_class (relname) VALUES ('x');

ERREUR 42501 : permission denied for schema pg_catalog

SELECT c.relname, c.relkind, c.relnatts, c.reltuples
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'ventes'
ORDER BY c.relkind, c.relname;

       relname       | relkind | relnatts | reltuples
---------------------+---------+----------+-----------
 article_pkey        | i       |        1 |       3.0
 client_pkey         | i       |        1 |       3.0
 code                | i       |        1 |       3.0
 commande_pkey       | i       |        1 |       3.0
 email               | i       |        1 |       3.0
 idx_commande_date   | i       |        1 |       3.0
 ligne_commande_pkey | i       |        2 |       4.0
 article             | r       |        5 |       3.0
 client              | r       |        6 |       3.0
 commande            | r       |        4 |       3.0
 ligne_commande      | r       |        4 |       4.0
 seq_facture         | S       |        3 |       1.0
 v_total_commande    | v       |        0 |       0.0
(13 lignes)

SELECT a.attnum, a.attname, format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull, a.attidentity, pg_get_expr(d.adbin, d.adrelid) AS defaut
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'client'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

 attnum | attname |            type             | attnotnull | attidentity |      defaut
--------+---------+-----------------------------+------------+-------------+-------------------
      1 | id      | integer                     | t          | d           |
      2 | nom     | character varying(80)       | t          |             |
      3 | email   | character varying(120)      | t          |             |
      4 | ville   | text                        | f          |             |
      5 | actif   | boolean                     | t          |             | true
      6 | cree_le | timestamp without time zone | f          |             | CURRENT_TIMESTAMP
(6 lignes)

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 conname, contype, conkey, confrelid::regclass AS cible, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'ligne_commande'::regclass
ORDER BY conname;

        conname        | contype | conkey |  cible   |                             definition
-----------------------+---------+--------+----------+---------------------------------------------------------------------
 CONSTRAINT_1          | c       |        | 0        | CHECK (("quantite" > 0))
 ligne_commande_ibfk_1 | f       | {1}    | commande | FOREIGN KEY (commande_id) REFERENCES commande(id) ON DELETE CASCADE
 ligne_commande_ibfk_2 | f       | {2}    | article  | FOREIGN KEY (article_id) REFERENCES article(id)
 ligne_commande_pkey   | p       | {1,2}  | 0        | PRIMARY KEY (commande_id, article_id)
(4 lignes)

SELECT a.attname
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid AND i.indisprimary
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY (i.indkey)
WHERE c.relname = 'ligne_commande'
ORDER BY a.attnum;

   attname
-------------
 commande_id
 article_id
(2 lignes)

SELECT conrelid::regclass AS table_enfant, conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'client'::regclass;

 table_enfant |     conname     |           pg_get_constraintdef(oid)
--------------+-----------------+-----------------------------------------------
 commande     | commande_ibfk_1 | FOREIGN KEY (client_id) REFERENCES client(id)
(1 ligne)

SELECT pg_get_viewdef('v_total_commande'::regclass);

 select "o"."id", "o"."client_id", sum(("l"."quantite" * "l"."prix_unit")) as "montant" from "commande" as "o" join "ligne_commande" as "l" on ("l"."commande_id" = "o"."id") group by "o"."id","o"."client_id";

SELECT seqrelid::regclass AS sequence, seqstart, seqincrement, seqmin, seqmax, seqcache, seqcycle
FROM pg_sequence;

  sequence   | seqstart | seqincrement | seqmin |       seqmax        | seqcache | seqcycle
-------------+----------+--------------+--------+---------------------+----------+----------
 seq_facture |     1000 |            1 |      1 | 9223372036854775806 |     1000 | f
(1 ligne)

SELECT p.proname, p.prokind, l.lanname, p.provolatile,
       pg_get_function_arguments(p.oid) AS arguments, pg_get_function_result(p.oid) AS resultat
FROM pg_proc p
JOIN pg_language l ON l.oid = p.prolang
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'ventes';

  proname  | prokind | lanname | provolatile |                    arguments                     |   resultat
-----------+---------+---------+-------------+--------------------------------------------------+---------------
 maj_stock | f       | plpgsql | v           |                                                  | trigger
 ttc       | f       | plpgsql | i           | ht numeric(10,2), taux numeric(4,2) DEFAULT 0.19 | numeric(10,2)
(2 lignes)

SELECT pg_get_functiondef('ttc'::regproc);

CREATE OR REPLACE FUNCTION ventes.ttc(ht numeric(10,2), taux numeric(4,2) DEFAULT 0.19)
 RETURNS numeric(10,2)
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
BEGIN
  RETURN round(ht * (1 + taux), 2);
END;
$function$

SELECT tgname, tgrelid::regclass AS table, tgfoid::regproc AS fonction, pg_get_triggerdef(oid) AS definition
FROM pg_trigger;

    tgname     |     table      | fonction  |                                                  definition
---------------+----------------+-----------+--------------------------------------------------------------------------------------------------------------
 trg_maj_stock | ligne_commande | maj_stock | CREATE TRIGGER trg_maj_stock AFTER INSERT ON ventes.ligne_commande FOR EACH ROW EXECUTE FUNCTION maj_stock()
(1 ligne)

COMMENT ON TABLE client IS 'Clients de la boutique';
COMMENT ON COLUMN client.email IS 'Adresse de contact, unique';
SELECT obj_description('client'::regclass, 'pg_class') AS de_table,
       col_description('client'::regclass, 3) AS de_colonne;

        de_table        |         de_colonne
------------------------+----------------------------
 Clients de la boutique | Adresse de contact, unique
(1 ligne)

SELECT rolname, rolsuper, rolcanlogin, rolpassword FROM pg_roles ORDER BY rolname;

 rolname | rolsuper | rolcanlogin | rolpassword
---------+----------+-------------+-------------
 app     | f        | t           | ********
 root    | t        | t           | ********
(2 lignes)

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'ventes' AND table_name IN ('client', 'commande')
ORDER BY table_name, constraint_name;

 constraint_name | table_name | constraint_type
-----------------+------------+-----------------
 client_pkey     | client     | PRIMARY KEY
 email           | client     | UNIQUE
 commande_ibfk_1 | commande   | FOREIGN KEY
 commande_pkey   | commande   | PRIMARY KEY
(4 lignes)

SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'ventes' AND referenced_table_name IS NOT NULL
ORDER BY table_name, constraint_name;

    constraint_name    |   table_name   | column_name | referenced_table_name | referenced_column_name
-----------------------+----------------+-------------+-----------------------+------------------------
 commande_ibfk_1       | commande       | client_id   | client                | id
 ligne_commande_ibfk_1 | ligne_commande | commande_id | commande              | id
 ligne_commande_ibfk_2 | ligne_commande | article_id  | article               | id
(3 lignes)

SHOW server_version;          -- 16.4
SHOW search_path;             -- ventes
SHOW sql_mode;                -- POSTGRESQL,STRICT_TRANS_TABLES
SHOW transaction_isolation;   -- read committed
SHOW statement_timeout;       -- 5min

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

EXPLAIN SELECT * FROM client WHERE id = 1;

 id | select_type | table  |  type  | possible_keys |   key   | key_len |  ref  | rows | Extra
----+-------------+--------+--------+---------------+---------+---------+-------+------+-------
  1 | SIMPLE      | client | eq_ref | PRIMARY,email | PRIMARY |         | const |    1 |
(1 ligne)

$env:PGPASSWORD = "password"
psql -h 127.0.0.1 -p 7007 -U root -d ventes
psql "host=server.example port=7007 user=root dbname=ventes sslmode=require"

 Schema |       Name       |   Type   | Owner
--------+------------------+----------+-------
 ventes | article          | table    | root
 ventes | client           | table    | root
 ventes | commande         | table    | root
 ventes | ligne_commande   | table    | root
 ventes | seq_facture      | sequence | root
 ventes | v_total_commande | view     | root
(6 lignes)

import psycopg2, psycopg2.extras

conn = psycopg2.connect(host="127.0.0.1", port=7007, user="app",
                        password="secret123", dbname="ventes")
cur = conn.cursor()

cur.execute("SELECT code, prix, stock FROM article WHERE prix > %s ORDER BY id", (2,))
for code, prix, stock in cur:
    print(code, prix, stock)          # CAH-96 2.50 200 / CLA-A4 4.90 80

cur.execute("INSERT INTO client (nom, email) VALUES (%(nom)s, %(email)s) RETURNING id",
            {"nom": "Nadia Haddad", "email": "[email protected]"})
identifiant = cur.fetchone()[0]
conn.commit()

import io

donnees = io.StringIO("GOM-01\tGomme blanche\t0.80\t300\nREG-30\tRègle 30 cm\t1.90\t120\n")
cur.copy_from(donnees, "article", columns=("code", "libelle", "prix", "stock"))

sortie = io.StringIO()
cur.copy_expert("COPY (SELECT code, libelle, prix FROM article ORDER BY id) "
                "TO STDOUT WITH (FORMAT csv, HEADER)", sortie)
print(sortie.getvalue())

code,libelle,prix
STY-01,Stylo bille,1.30
CAH-96,Cahier 96 pages,2.50
CLA-A4,Classeur A4,4.90
GOM-01,Gomme blanche,0.80
REG-30,Règle 30 cm,1.90

await using var conn = new NpgsqlConnection("Host=serveur;Port=7007;Username=app;Password=…;Database=ventes");
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand("SELECT libelle, prix FROM article WHERE prix > $1", conn);
cmd.Parameters.Add(new NpgsqlParameter { Value = 2.0m, NpgsqlDbType = NpgsqlDbType.Numeric });

import pyodbc

cn = pyodbc.connect("DRIVER={MIRAJ ODBC Driver};SERVER=127.0.0.1;PORT=7007;"
                    "UID=app;PWD=secret123;DATABASE=ventes")
cur = cn.cursor()
cur.execute("SELECT id, nom, actif FROM client WHERE ville = ?", "Alger")
print(cur.fetchall())                 # [(1, 'Amel Kaci', True)]
cur.execute("INSERT INTO client (nom, email) VALUES (?, ?) RETURNING id", "Essai", "[email protected]")
print(cur.fetchone()[0])
cn.commit()

import miraj

conn = miraj.connect(host="127.0.0.1", port=7007, user="app", password="secret123",
                     dbname="ventes", protocol="postgresql")
cur = conn.cursor()
cur.execute("SELECT libelle, prix FROM article WHERE prix > %s", (2,))

SELECT n.nspname AS schema, c.relname AS table,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS taille,
       obj_description(c.oid, 'pg_class') AS commentaire
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'miraj')
ORDER BY 1, 2;

 schema |     table      |  taille   |      commentaire
--------+----------------+-----------+------------------------
 ventes | article        | 589 bytes |
 ventes | client         | 732 bytes | Clients de la boutique
 ventes | commande       | 344 bytes |
 ventes | ligne_commande | 176 bytes |
(4 lignes)

SELECT to_regclass('client') IS NOT NULL AS client, to_regclass('absente') IS NOT NULL AS absente;

 client | absente
--------+---------
 t      | f
(1 ligne)

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

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

SELECT typname, oid, typcategory FROM pg_type
WHERE typname IN ('bool', 'int4', 'numeric', 'text', 'timestamptz', 'jsonb', 'uuid', 'vector')
ORDER BY typname;

   typname   |  oid  | typcategory
-------------+-------+-------------
 bool        |    16 | B
 int4        |    23 | N
 jsonb       |  3802 | U
 numeric     |  1700 | N
 text        |    25 | S
 timestamptz |  1184 | D
 uuid        |  2950 | U
 vector      | 16385 | U
(8 lignes)

SELECT pid, usename, datname, client_addr, state, query FROM pg_stat_activity ORDER BY pid;

 pid | usename | datname | client_addr | state  |                                       query
-----+---------+---------+-------------+--------+------------------------------------------------------------------------------------
   1 | root    | miraj   | localhost   | idle   |
  28 | root    | miraj   | localhost   | active | SELECT pid, usename, datname, client_addr, state, query FROM pg_stat_activity ORDER BY pid
(2 lignes)

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'idle';

 pg_terminate_backend
----------------------
 t
 t
(2 lignes)

SELECT name, setting FROM pg_settings
WHERE name IN ('server_version', 'server_version_num', 'client_encoding', 'DateStyle', 'TimeZone',
               'max_connections', 'password_encryption', 'search_path', 'transaction_isolation',
               'statement_timeout')
ORDER BY name;

         name          |    setting
-----------------------+----------------
 DateStyle             | ISO, MDY
 TimeZone              | Etc/GMT-1
 client_encoding       | UTF8
 max_connections       | 151
 password_encryption   | scram-sha-256
 search_path           | ventes
 server_version        | 16.4
 server_version_num    | 160004
 statement_timeout     | 5min
 transaction_isolation | read committed
(10 lignes)

$env:MIRAJ_PASSWORD = "password"
miraj-cli.exe --port 7007 -d ventes

OK, 0 ligne(s) affectée(s)
MIRAJ 1.0.0 - connecté (langage PostgreSQL) - tapez \help pour l'aide, \q pour quitter.
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

BACKUP DATABASE ventes TO 'sauv1';

 Database |      Directory       | Files | Bytes | End_lsn
----------+----------------------+-------+-------+---------
 ventes   | E:\sauvegardes\sauv1 |     9 |  7247 |    1464
(1 ligne)

RESTORE DATABASE ventes_copie FROM 'sauv1';

WARNING:  base ventes restaurée sous le nom ventes_copie : les références qualifiées par l'ancien nom (vues, routines, clés étrangères) le gardent
   Database   |      Directory       | Files | Bytes | End_lsn
--------------+----------------------+-------+-------+---------
 ventes_copie | E:\sauvegardes\sauv1 |     9 |  7247 |    1464
(1 ligne)

$env:MIRAJ_PASSWORD = "password"
miraj-backup backup  --db ventes --to sauv2 --port 7007
miraj-backup restore --from sauv2 --db ventes_copie --replace --port 7007
miraj-backup verify  --from E:\sauvegardes\sauv2
miraj-backup list    --from E:\sauvegardes\sauv2

ventes : 9 fichier(s), 7.1 Ko, position 1464 → E:\sauvegardes\sauv2
ventes_copie : 9 fichier(s), 7.1 Ko, position 1464 → E:\sauvegardes\sauv2
Sauvegarde valide : base ventes, 9 fichier(s), 7.1 Ko, position 1464

miraj-dump --db ventes --users -r ventes.sql --port 7007
miraj-dump restore ventes.sql --port 7008
psql -h 127.0.0.1 -p 7008 -U root -d miraj -v ON_ERROR_STOP=1 -f ventes.sql