34. PostgreSQL : Langage de définition des données (DDL)

Ce chapitre décrit les instructions qui créent, modifient et suppriment les schémas, les tables, les contraintes, les index, les vues et les séquences d'une instance MIRAJ en langage PostgreSQL. Les types de colonnes, les valeurs par défaut, les colonnes d'identité et les colonnes générées sont décrits au chapitre 33. Types de données.

Les exemples s'appuient sur une base de vente, créée en 34.2.

34.1 Schémas#

Une base MIRAJ par schéma#

Une instance en langage PostgreSQL présente une seule base PostgreSQL, nommée miraj ; chaque base de données de MIRAJ y est un schéma. Créer un schéma crée donc une base MIRAJ, avec ses propres tables, et la supprimer supprime tout son contenu.

CREATE SCHEMA [IF NOT EXISTS] nom [AUTHORIZATION compte] [élément …];
CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION compte [élément …];
DROP SCHEMA [IF EXISTS] nom [CASCADE | RESTRICT];

CREATE SCHEMA ventes;
CREATE SCHEMA IF NOT EXISTS ventes;       -- avis, pas d'erreur
SELECT nspname FROM pg_namespace ORDER BY nspname;

 nspname
--------------------
 information_schema
 miraj
 pg_catalog
 ventes

pg_catalog et information_schema sont les schémas du catalogue ; miraj est la base système (comptes et droits), visible du seul compte root. CREATE DATABASE nom et DROP DATABASE nom sont acceptés comme synonymes de CREATE SCHEMA et DROP SCHEMA.

AUTHORIZATION compte : un schéma n'a pas de propriétaire dans MIRAJ ; le compte nommé reçoit tous les privilèges sur le schéma (GRANT ALL PRIVILEGES ON schéma.* TO compte), ce qui demande de pouvoir les accorder. Rien n'est accordé quand le compte est celui de la session (AUTHORIZATION CURRENT_USER). Sans nom de schéma (CREATE SCHEMA AUTHORIZATION app), le schéma porte le nom du compte. Un compte inconnu rend 42704 (role "…" does not exist) et le schéma n'est pas créé. Les autres droits se donnent par GRANT (voir 39. Comptes et privilèges).

Éléments de schéma : comme dans PostgreSQL, CREATE SCHEMA peut créer d'un coup les objets du schéma, écrits à la suite sans ; :

CREATE SCHEMA ventes
    CREATE TABLE client (id integer PRIMARY KEY, nom text NOT NULL)
    CREATE TABLE commande (id integer PRIMARY KEY, client_id integer REFERENCES client (id))
    CREATE INDEX commande_client_idx ON commande (client_id)
    CREATE SEQUENCE num_facture
    CREATE VIEW client_actif AS SELECT c.nom FROM client c JOIN commande o ON o.client_id = c.id
    GRANT SELECT ON client TO lecture;

Les éléments admis sont CREATE TABLE, CREATE [OR REPLACE] VIEW, CREATE [UNIQUE] INDEX, CREATE SEQUENCE, CREATE TRIGGER (sa fonction, qui vient d'un autre schéma, s'écrit qualifiée : EXECUTE FUNCTION outils.f()) et GRANT. Ils sont créés dans le schéma, dans l'ordre écrit ; le schéma courant de la session ne change pas. C'est tout ou rien : si un élément échoue, le schéma qui vient d'être créé est retiré avec ce qu'il contenait déjà, et l'erreur de l'élément est rendue.

CREATE SCHEMA public rend 42P06 (schema "public" already exists) : public est le nom du schéma courant (voir plus bas), il existe donc toujours et aucune base de ce nom n'est créée. CREATE SCHEMA IF NOT EXISTS public est sans effet (un avis).

DROP SCHEMA supprime toujours le schéma et tout son contenu, avec CASCADE, avec RESTRICT ou sans rien : RESTRICT ne protège pas un schéma qui contient des tables, alors que PostgreSQL le refuse.

Un nom de table compte au plus 64 caractères. Un nom de colonne admet les lettres et les chiffres non ASCII (prénom, "Âge", 64 caractères au plus) : écrit sans guillemets, un nom qui porte une lettre non ASCII est replié en minuscules comme dans PostgreSQL (PRÉNOM et Prénom nomment prénom) ; entre guillemets il est gardé tel quel, et sa casse compte pour ses lettres non ASCII ("Âge" n'est pas âge). Un nom d'index les admet aussi. Un nom de table, de vue, de séquence, de schéma ou de routine, lui, n'admet pas les lettres accentuées, même entre guillemets : CREATE TABLE "événement" (…) rend 42000 (Incorrect table name 'événement'). Les signes combinants (lettre accentuée en forme décomposée) et la ponctuation sont refusés partout. Les espaces et les majuscules sont admis entre guillemets ("Table Mixte"), le nom restant insensible à la casse de ses lettres ASCII.

Schéma courant et search_path#

Le schéma courant est celui où sont cherchées et créées les tables écrites sans qualification. Il est fixé par le nom de base donné à la connexion, quand il désigne un schéma, puis par SET search_path :

SET search_path TO ventes;
SELECT current_database(), current_schema();

 current_database() | current_schema()
--------------------+------------------
 miraj              | ventes

• SET search_path TO a, b, public retient le premier schéma de la liste qui existe ; les suivants ne sont pas consultés. Si aucun n'existe, le schéma courant est gardé, sans erreur.
• SHOW search_path rend le schéma courant (public tant qu'aucun n'est choisi).
• USE ventes est accepté et fait de même.
• Sans schéma courant, créer une table non qualifiée échoue (3F000, no schema has been selected to create in) ; c'est aussi le cas après la suppression du schéma courant.

public n'est pas un schéma : dans un nom, public. désigne le schéma courant. public.client et client sont la même table, et CREATE TABLE public.t (…) crée t dans le schéma courant.

Une table d'un autre schéma se nomme schéma.table (ventes.client). La forme à trois parties miraj.ventes.client n'est pas reconnue (42601).

Les variables de session et le chemin de recherche sont détaillés au chapitre 38. Transactions et session.

Ce qui n'existe pas#

ALTER SCHEMA … RENAME TO et ALTER SCHEMA … OWNER TO sont refusés (0A000, This version of Miraj doesn't yet support 'ALTER').

34.2 CREATE TABLE#

Syntaxe#

CREATE [TEMP | TEMPORARY | UNLOGGED] TABLE [IF NOT EXISTS] [schéma.]table (
    définition_colonne [, …]
    [, [CONSTRAINT nom] PRIMARY KEY (colonne [, …])]
    [, [CONSTRAINT nom] UNIQUE (colonne [, …])]
    [, [CONSTRAINT nom] CHECK (condition)]
    [, [CONSTRAINT nom] FOREIGN KEY (colonne [, …])
           REFERENCES table_ref [(colonne [, …])]
           [ON DELETE action] [ON UPDATE action]]
);

où définition_colonne s'écrit :

colonne type
    [COLLATE collation]
    [[CONSTRAINT nom] NOT NULL | NULL]
    [DEFAULT expression]
    [GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY]
    [GENERATED ALWAYS AS (expression) STORED]
    [[CONSTRAINT nom] PRIMARY KEY]
    [[CONSTRAINT nom] UNIQUE]
    [[CONSTRAINT nom] CHECK (condition)]
    [[CONSTRAINT nom] REFERENCES table_ref [(colonne)] [ON DELETE action] [ON UPDATE action]]

action vaut NO ACTION, RESTRICT, CASCADE ou SET NULL.

La base d'exemple#

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE TABLE client (
    id       serial PRIMARY KEY,
    code     varchar(20) NOT NULL UNIQUE,
    nom      varchar(100) NOT NULL,
    email    varchar(255),
    plafond  numeric(12,2) NOT NULL DEFAULT 0 CHECK (plafond >= 0),
    cree_le  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE article (
    id       serial PRIMARY KEY,
    ref      varchar(20) NOT NULL,
    libelle  text NOT NULL,
    prix_ht  numeric(10,2) NOT NULL,
    stock    integer NOT NULL DEFAULT 0,
    CONSTRAINT article_ref_uq UNIQUE (ref),
    CONSTRAINT article_prix_ck CHECK (prix_ht >= 0)
);

CREATE TABLE commande (
    id          bigserial PRIMARY KEY,
    client_id   integer NOT NULL REFERENCES client (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    passee_le   date NOT NULL DEFAULT CURRENT_DATE,
    statut      varchar(12) NOT NULL DEFAULT 'nouvelle',
    CONSTRAINT commande_statut_ck CHECK (statut IN ('nouvelle', 'expediee', 'annulee'))
);

CREATE TABLE ligne_commande (
    commande_id  bigint NOT NULL,
    no_ligne     smallint NOT NULL,
    article_id   integer NOT NULL,
    quantite     integer NOT NULL CHECK (quantite > 0),
    prix_unit    numeric(10,2) NOT NULL,
    montant      numeric(12,2) GENERATED ALWAYS AS (quantite * prix_unit) STORED,
    PRIMARY KEY (commande_id, no_ligne),
    CONSTRAINT ligne_commande_fk FOREIGN KEY (commande_id) REFERENCES commande (id) ON DELETE CASCADE,
    CONSTRAINT ligne_article_fk FOREIGN KEY (article_id) REFERENCES article (id)
);

INSERT INTO client (code, nom, email) VALUES ('C001', 'Dupont', '[email protected]'), ('C002', 'Martin', NULL);
INSERT INTO article (ref, libelle, prix_ht, stock) VALUES ('STY-01', 'Stylo', 1.50, 100), ('CAH-01', 'Cahier', 3.20, 40);
INSERT INTO commande (client_id) VALUES (1);
INSERT INTO ligne_commande (commande_id, no_ligne, article_id, quantite, prix_unit)
VALUES (1, 1, 1, 10, 1.50), (1, 2, 2, 2, 3.20);

SELECT * FROM ligne_commande ORDER BY no_ligne;

 commande_id | no_ligne | article_id | quantite | prix_unit | montant
-------------+----------+------------+----------+-----------+---------
 1           | 1        | 1          | 10       | 1.50      | 15.00
 1           | 2        | 2          | 2        | 3.20      | 6.40

Erreurs de création#

Avec IF NOT EXISTS, une table déjà présente est laissée telle quelle : l'instruction rend CREATE TABLE et un avis (NOTICE: relation "client" already exists, skipping).

Tables temporaires#

CREATE TEMP TABLE panier (article_id integer, quantite integer);

TEMP ou TEMPORARY crée une table propre à la session : les autres sessions ne la voient pas (42P01), et elle disparaît à la fermeture de la connexion. Elle n'apparaît ni dans SHOW TABLES ni dans pg_tables. Une table temporaire masque une table permanente de même nom tant qu'elle existe ; DROP TABLE supprime alors la table temporaire.

ON COMMIT PRESERVE ROWS (le défaut) garde les lignes après COMMIT. Avec ON COMMIT DELETE ROWS, la table temporaire est vidée à chaque validation — y compris, hors d'un bloc, à la fin de chaque instruction, qui est sa propre transaction ; son compteur d'identité continue. Cette clause n'est admise que sur une table temporaire (0A000 sinon).

Avec ON COMMIT DROP, la table temporaire est supprimée à la fin de la transaction où elle a été créée, validée ou annulée :

BEGIN;
CREATE TEMP TABLE lot (id integer, montant numeric(12,2)) ON COMMIT DROP;
INSERT INTO lot VALUES (1, 10.00);
COMMIT;
SELECT * FROM lot;        -- ERREUR 42P01 : la table n'existe plus

Hors d'un bloc, l'instruction est sa propre transaction : la table ne lui survit pas. Une instruction de définition qui valide la transaction d'elle-même (voir 34.11) la supprime aussi. Comme ON COMMIT DELETE ROWS, la clause n'est admise que sur une table temporaire (0A000 sinon).

CREATE UNLOGGED TABLE crée une table ordinaire.

CREATE TABLE … (LIKE …)#

CREATE TABLE article_modele (LIKE article);

Crée une table vide de même structure. La copie est complète : colonnes, valeurs par défaut, identité, clé primaire, contraintes d'unicité et CHECK sont repris ; les options INCLUDING … / EXCLUDING … sont acceptées sans effet. LIKE doit être le seul élément de la liste ((LIKE article, extra integer) : 42601).

CREATE TABLE … AS#

CREATE TABLE article_cher AS SELECT id, ref, prix_ht FROM article WHERE prix_ht > 2;

Crée la table depuis le résultat d'une requête et y copie les lignes ; l'instruction rend SELECT n, n étant le nombre de lignes copiées. Les colonnes prennent le type et le NOT NULL de la requête, sans clé primaire, identité, valeur par défaut ni contrainte :

SHOW CREATE TABLE article_cher;

CREATE TABLE "article_cher" (
  "id" integer NOT NULL,
  "ref" character varying(20) NOT NULL,
  "prix_ht" numeric(10,2) NOT NULL
)

CREATE TABLE IF NOT EXISTS … AS laisse une table existante intacte (SELECT 0 et un avis). Les formes WITH NO DATA, CREATE TABLE t (a, b) AS …, CREATE TABLE t AS TABLE u et SELECT … INTO t ne sont pas prises en charge (voir 34.13).

Clauses de stockage#

TABLESPACE nom et INHERITS (table) sont acceptés sans effet : la table créée par CREATE TABLE t (a integer) INHERITS (article) n'a que la colonne a et n'hérite de rien. WITH (fillfactor = …) est une erreur de syntaxe. Le partitionnement s'écrit dans la syntaxe de MIRAJ (voir 34.12).

34.3 Contraintes#

Vue d'ensemble#

CONSTRAINT nom devant une contrainte UNIQUE, CHECK ou de clé étrangère fixe son nom. Les noms par défaut sont ceux de MIRAJ, et non ceux de PostgreSQL (client_code_key, client_plafond_check, commande_client_id_fkey) : nommer explicitement les contraintes que l'on compte modifier ou citer dans un ON CONFLICT ON CONSTRAINT.

SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid IN ('client'::regclass, 'commande'::regclass)
ORDER BY conrelid, contype, conname;

 conname            | contype | pg_get_constraintdef(oid)
--------------------+---------+------------------------------------------------------------------------------------
 CONSTRAINT_1       | c       | CHECK (("plafond" >= 0))
 client_pkey        | p       | PRIMARY KEY (id)
 code               | u       | UNIQUE (code)
 commande_statut_ck | c       | CHECK (("statut" in ('nouvelle','expediee','annulee')))
 commande_ibfk_1    | f       | FOREIGN KEY (client_id) REFERENCES client(id) ON UPDATE CASCADE ON DELETE RESTRICT
 commande_pkey      | p       | PRIMARY KEY (id)

NOT NULL#

Une colonne NOT NULL refuse NULL (23502, Column 'a' cannot be null) ; l'omettre dans un INSERT alors qu'elle n'a pas de valeur par défaut est la même erreur (Field 'a' doesn't have a default value). NULL écrit explicitement autorise NULL, ce qui est le défaut. Un nom donné à une contrainte NOT NULL (CONSTRAINT x NOT NULL) est accepté et oublié.

Clé primaire#

Une table a au plus une clé primaire, sur une ou plusieurs colonnes ; ses colonnes deviennent NOT NULL. Elle se nomme toujours <table>_pkey dans le catalogue : un nom donné par CONSTRAINT nom PRIMARY KEY est ignoré. Un doublon est refusé :

ERROR 23505: Duplicate entry '1' for key 'PRIMARY'

Unicité#

UNIQUE refuse deux lignes de mêmes valeurs ; plusieurs lignes peuvent porter NULL. Sur une colonne de texte, les valeurs sont comparées octet par octet : 'c001' n'est pas un doublon de 'C001'.

INSERT INTO client (code, nom) VALUES ('c001', 'Minuscules');   -- INSERT 0 1
INSERT INTO client (code, nom) VALUES ('C001', 'Doublon');
-- ERROR 23505: Duplicate entry 'C001' for key 'code'

Déclarer la colonne COLLATE utf8mb4_general_ci pour une unicité qui ignore la casse et les accents (voir 33.5).

CHECK#

La condition est évaluée à chaque écriture de la ligne ; elle est satisfaite si elle est vraie ou NULL. Une contrainte écrite sur une colonne peut citer les autres colonnes de la ligne.

INSERT INTO commande (client_id, statut) VALUES (1, 'perdue');
-- ERROR 23514: new row for relation "commande" violates check constraint "commande_statut_ck"

La condition n'admet ni sous-requête, ni fonction dont le résultat change : CHECK (a > (SELECT 1)) et CHECK (a < now()) sont refusés (XX000, An expression of a check constraint 'x' contains disallowed function., suivi du nom de la fonction pour now()). Elle garde les règles du langage PostgreSQL (opérateur ~, length en caractères) même si l'instance est plus tard rouverte en langage MIRAJ.

Clés étrangères#

colonne type REFERENCES table_ref [(colonne)] [ON DELETE action] [ON UPDATE action]
[CONSTRAINT nom] FOREIGN KEY (colonne [, …]) REFERENCES table_ref [(colonne [, …])]
    [ON DELETE action] [ON UPDATE action]

INSERT INTO commande (client_id) VALUES (99);
-- ERROR 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") ON DELETE RESTRICT ON UPDATE CASCADE)

DELETE FROM client WHERE id = 1;
-- ERROR 23503: Cannot delete or update a parent row: a foreign key constraint fails (…)

UPDATE client SET id = 50 WHERE id = 1;      -- ON UPDATE CASCADE : commande.client_id passe à 50
DELETE FROM commande WHERE id = 1;           -- ON DELETE CASCADE : ses lignes sont supprimées

Règles :

• REFERENCES table sans colonne désigne la clé primaire de la table référencée (42830, there is no primary key for referenced table "sanspk", si elle n'en a pas).
• Les colonnes référencées doivent porter la clé primaire ou une contrainte UNIQUE.
• Les colonnes des deux côtés doivent être de types compatibles.
• Une clé étrangère peut référencer sa propre table (arborescence), et porter sur plusieurs colonnes.
• Une colonne fille NULL n'est pas vérifiée.
• La table référencée doit être dans le même schéma : une référence à autre_schema.table est refusée (0A000, This version of Miraj doesn't yet support 'FOREIGN KEY referencing another database').

DEFERRABLE, INITIALLY DEFERRED, INITIALLY IMMEDIATE et MATCH FULL sont acceptés sans effet : une clé étrangère est toujours vérifiée à l'instruction, jamais à la fin de la transaction. Insérer la ligne référencée avant celle qui la cite.

34.4 ALTER TABLE#

ALTER TABLE [ONLY] [schéma.]table [*] action [, action …];

ONLY (celui qu'écrit pg_dump : ALTER TABLE ONLY ventes.client ADD CONSTRAINT …) et * sont acceptés sans effet : MIRAJ n'a pas d'héritage de tables.

Plusieurs actions séparées par des virgules s'appliquent en une instruction :

ALTER TABLE client ADD COLUMN telephone varchar(20);
ALTER TABLE client
    ALTER COLUMN telephone SET DEFAULT '',
    ADD COLUMN ville text;

Colonnes#

ALTER TABLE client ADD COLUMN pays char(2) NOT NULL DEFAULT 'FR',
                   ADD COLUMN remise numeric(5,2) DEFAULT 0 CHECK (remise <= 100);
ALTER TABLE client ALTER COLUMN telephone TYPE varchar(30);
ALTER TABLE client RENAME COLUMN telephone TO tel;
ALTER TABLE client DROP COLUMN tel;

Ajout. Les lignes existantes reçoivent la valeur par défaut de la nouvelle colonne, NULL sans DEFAULT. Une colonne NOT NULL ajoutée sans valeur par défaut à une table qui contient des lignes est refusée, comme dans PostgreSQL, et la table reste inchangée :

ALTER TABLE client ADD COLUMN nb integer NOT NULL;
-- ERROR 23502: column "nb" of relation "client" contains null values

ADD COLUMN IF NOT EXISTS d'une colonne présente et DROP COLUMN IF EXISTS d'une colonne absente rendent un avis (column "pays" of relation "client" already exists, skipping).

Changement de type. Les valeurs sont converties ; sans USING, la conversion habituelle du type s'applique (un texte fait de chiffres devient un entier). USING n'admet que la colonne elle-même, convertie ou non (USING telephone, USING telephone::integer) ; une autre expression est refusée :

ALTER TABLE client ALTER COLUMN telephone TYPE text USING telephone::text || 'x';
-- ERROR 0A000: ALTER COLUMN ... TYPE ... USING an expression other than the column is not supported yet in the PostgreSQL language

Une valeur qui ne se convertit pas fait échouer l'instruction, et la table reste inchangée :

ALTER COLUMN … TYPE jsonb (ou json) sur une colonne JSON ne change que le type annoncé.

Suppression. DROP COLUMN retire aussi les contraintes d'unicité et les index qui ne portaient que sur cette colonne. Elle est refusée (XX000), même avec CASCADE, tant que la colonne est citée ailleurs :

Supprimer d'abord la contrainte ou la colonne générée ; un index sur une expression qui cite la colonne compte comme une colonne générée. Une vue qui cite la colonne n'empêche pas sa suppression : c'est sa prochaine lecture qui échoue (XX000, View 'ventes.v_tel' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them).

Contraintes#

ALTER TABLE client ADD CONSTRAINT client_email_uq UNIQUE (email);
ALTER TABLE client ADD CONSTRAINT client_pays_ck CHECK (pays IN ('FR', 'BE', 'DZ'));
ALTER TABLE commande ADD COLUMN livreur_id integer;
ALTER TABLE commande ADD CONSTRAINT commande_livreur_fk
    FOREIGN KEY (livreur_id) REFERENCES client (id) ON DELETE SET NULL;
ALTER TABLE commande DROP CONSTRAINT commande_livreur_fk;

Une contrainte ajoutée est vérifiée aussitôt sur les lignes existantes ; si l'une la viole, l'instruction échoue et rien n'est ajouté (23505 pour une unicité ou une clé primaire, 23514 pour un CHECK). NOT VALID et VALIDATE CONSTRAINT ne sont pas pris en charge.

Pour changer une contrainte, la supprimer et la recréer dans la même instruction :

ALTER TABLE commande
    DROP CONSTRAINT commande_statut_ck,
    ADD CONSTRAINT commande_statut_ck
        CHECK (statut IN ('nouvelle', 'expediee', 'annulee', 'livree'));

DROP CONSTRAINT d'un nom inconnu rend 42704 (constraint "absente" of relation "commande" does not exist), un simple avis avec IF EXISTS. La clé primaire se supprime sous le nom <table>_pkey ; celle d'une colonne d'identité (serial) ne peut pas l'être (42000), car une telle colonne doit porter une clé.

Renommer la table#

ALTER TABLE client RENAME TO clients;

Les clés étrangères des autres tables suivent le nouveau nom. Renommer vers un nom déjà pris rend 42P07. Le nom de la clé primaire dans le catalogue suit la table (clients_pkey).

Formes de MIRAJ#

Les formes propres à MIRAJ restent acceptées : ADD COLUMN c type FIRST ou AFTER autre (place de la colonne), MODIFY colonne type …, CHANGE ancien nouveau type …, DROP INDEX nom, ainsi que la gestion des partitions. Elles sont décrites dans le chapitre 7.

Formes refusées#

ALTER TABLE IF EXISTS, RENAME ancien TO nouveau sans le mot COLUMN, RENAME CONSTRAINT, SET SCHEMA, OWNER TO, ALTER COLUMN … SET STATISTICS, ENABLE ROW LEVEL SECURITY, VALIDATE CONSTRAINT et ADD CONSTRAINT … NOT VALID rendent 0A000 (This version of Miraj doesn't yet support 'ALTER TABLE'). ALTER COLUMN … ADD GENERATED … AS IDENTITY, DROP IDENTITY et DROP EXPRESSION sont des erreurs de syntaxe (42601).

34.5 DROP TABLE et TRUNCATE#

DROP TABLE#

DROP TABLE [IF EXISTS] [schéma.]table [, …] [CASCADE | RESTRICT];

Supprime la table, ses lignes, ses index et ses contraintes. Une liste de tables est supprimée tout entière ou pas du tout : si l'une manque (42P01), aucune n'est supprimée.

CASCADE supprime aussi les vues qui lisent la table. Il ne retire pas les clés étrangères des autres tables, contrairement à PostgreSQL : supprimer d'abord la table fille, ou sa contrainte (ALTER TABLE commande DROP CONSTRAINT commande_ibfk_1).

TRUNCATE#

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

Vide la table ; l'instruction rend l'étiquette TRUNCATE TABLE. Comme dans PostgreSQL, le compteur d'une colonne d'identité continue sans option ou avec CONTINUE IDENTITY, et repart de sa valeur de départ avec RESTART IDENTITY :

CREATE TABLE tr (id serial PRIMARY KEY, a integer);
INSERT INTO tr (a) VALUES (1), (2);
TRUNCATE tr;
INSERT INTO tr (a) VALUES (1) RETURNING id;      -- 3
TRUNCATE tr RESTART IDENTITY;
INSERT INTO tr (a) VALUES (1) RETURNING id;      -- 1

TRUNCATE commande;
-- ERROR 0A000: Cannot truncate a table referenced in a foreign key constraint ("ventes"."ligne_commande", CONSTRAINT "ligne_commande_fk" FOREIGN KEY ("commande_id") REFERENCES "commande" ("id") ON DELETE CASCADE)

TRUNCATE commande CASCADE;                 -- vide aussi ligne_commande

• Une table référencée par une clé étrangère ne se vide qu'avec CASCADE, qui vide aussi, de proche en proche, les tables qui la référencent.
• Une seule table par instruction : TRUNCATE a, b rend 0A000 (TRUNCATE of several tables is not supported yet in the PostgreSQL language).
• Dans un bloc de transaction, TRUNCATE s'annule par ROLLBACK, comme dans PostgreSQL (voir 35.6 et 34.11).

34.6 Index#

CREATE [UNIQUE] INDEX [IF NOT EXISTS] nom ON [schéma.]table [USING btree] (élément [, …]);
DROP INDEX [IF EXISTS] [schéma.]nom [CASCADE | RESTRICT];

où élément est une colonne ou une expression entre parenthèses.

CREATE INDEX commande_client_idx ON commande (client_id);
CREATE INDEX commande_date_statut_idx ON commande USING btree (passee_le DESC, statut);
CREATE UNIQUE INDEX client_email_idx ON client (email);
CREATE INDEX article_libelle_idx ON article ((lower(libelle)));

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

 indexname                | indexdef
--------------------------+--------------------------------------------------------------------------------------
 commande_client_idx      | CREATE INDEX commande_client_idx ON ventes.commande USING btree (client_id)
 commande_date_statut_idx | CREATE INDEX commande_date_statut_idx ON ventes.commande USING btree (passee_le, statut)
 commande_pkey            | CREATE UNIQUE INDEX commande_pkey ON ventes.commande USING btree (id)

• Le nom est obligatoire : CREATE INDEX ON commande (passee_le) est une erreur de syntaxe. Il ne se qualifie pas par un schéma ; l'index vit dans le schéma de sa table.
• Un nom déjà pris sur la table rend 42P07 (Duplicate key name 'commande_client_idx'), un avis avec IF NOT EXISTS. Deux tables peuvent avoir un index de même nom.
• CREATE UNIQUE INDEX refuse les doublons comme une contrainte UNIQUE ; il échoue (23505) si la table en contient déjà.
• La clé primaire et chaque contrainte UNIQUE ont leur index, visible dans pg_indexes (<table>_pkey, nom de la contrainte).
• ASC, DESC, NULLS FIRST et NULLS LAST après une colonne ((nom DESC NULLS LAST)) sont acceptés sans effet : l'index est toujours croissant, et l'ordre d'un résultat reste celui que demande la requête. USING hash crée un index ordinaire (btree).
• L'index est construit pendant l'instruction ; CONCURRENTLY n'est pas reconnu.

Index sur une expression#

Un élément entre parenthèses est une expression : ((lower(libelle))), ((doc ->> 'ville')), ((libelle || '-' || ref)), seule ou mêlée à des colonnes ((stock, (lower(ref)))). L'index sert une condition expression = constante écrite avec la même expression :

EXPLAIN SELECT * FROM article WHERE lower(libelle) = 'stylo';

 id | select_type | table   | type | possible_keys                              | key                 | key_len | ref   | rows | Extra
----+-------------+---------+------+--------------------------------------------+---------------------+---------+-------+------+-------------
 1  | SIMPLE      | article | ref  | PRIMARY,article_ref_uq,article_libelle_idx | article_libelle_idx | NULL    | const | 1    | Using where

• L'expression doit être déterministe : ((libelle || now())) est refusé (XX000).
• L'index ne sert ni un intervalle (lower(libelle) > 'a'), ni un IN, ni un ORDER BY sur l'expression : EXPLAIN montre alors un parcours complet (type = ALL).
• CREATE UNIQUE INDEX sur une expression est refusé (0A000, UNIQUE index on an expression is not supported yet in the PostgreSQL language).
• Après une expression, une classe d'opérateurs ou NULLS FIRST | LAST sont acceptés sans effet ; après une simple colonne, NULLS FIRST | LAST de même, mais une classe d'opérateurs est une erreur de syntaxe.
• Une colonne citée par l'expression ne peut pas être supprimée tant que l'index existe.

EXPLAIN rend le plan de MIRAJ (voir 36. Requêtes SELECT).

DROP INDEX#

DROP INDEX commande_client_idx;

DROP INDEX ne nomme pas la table, comme dans PostgreSQL : l'index est cherché parmi les tables du schéma courant, ou du schéma écrit (DROP INDEX ventes.commande_client_idx).

Un nom d'index est unique dans le schéma, comme dans PostgreSQL où un index est une relation : CREATE INDEX nom (ou ALTER TABLE … ADD CONSTRAINT nom UNIQUE) est refusé avec 42P07 (relation "nom" already exists) quand ce nom est déjà celui d'un index d'une autre table du schéma, d'une table, d'une vue ou d'une séquence ; CREATE INDEX IF NOT EXISTS ne fait alors rien. Les tables temporaires ont leur propre espace de noms.

Seuls les noms écrits comptent. L'index implicite d'une colonne UNIQUE sans nom de contrainte porte dans MIRAJ le nom de la colonne (PostgreSQL l'appellerait client_email_key) : il ne prend pas ce nom dans le schéma, et CREATE INDEX email ON contact (email) passe alors qu'une autre table a une colonne email UNIQUE. MIRAJ ne gardant pas l'origine d'un nom, est tenu pour implicite un index d'unicité qui porte le nom de sa première colonne (suivi ou non de _2, _3…) ; un CREATE UNIQUE INDEX email ON client (email) écrit ainsi est donc traité de même. Sur la table même qui porte l'index implicite, le nom reste pris (un nom d'index est unique dans sa table).

DROP INDEX nom vise l'index au nom écrit quand des index implicites portent le même nom. Deux tables peuvent malgré tout porter un index de même nom au même titre : index créés en langage MIRAJ (où le nom n'est unique que dans sa table), ou colonnes UNIQUE de même nom dans deux tables sans index au nom écrit. DROP INDEX nom est alors refusé plutôt que d'en retirer un au hasard ; écrire la table :

DROP INDEX email;             -- 42710 : index name "email" is ambiguous: tables "client" and "contact" both have …
DROP INDEX email ON contact;  -- forme de MIRAJ, acceptée : celui de cette table

Un index créé sur la colonne fille d'une clé étrangère (CREATE INDEX … ON commande (client_id)) se retire comme un autre : comme PostgreSQL, MIRAJ n'exige pas d'index sur cette colonne en langage PostgreSQL. La clé étrangère reste contrôlée à chaque écriture ; sans l'index, la suppression ou la modification d'une ligne parente lit la table fille en entier.

DROP INDEX du nom d'une contrainte UNIQUE supprime la contrainte (PostgreSQL exige DROP CONSTRAINT).

Ce qui n'existe pas#

Index partiels (WHERE), colonnes incluses (INCLUDE), USING gin, USING gist et USING brin, CONCURRENTLY, ALTER INDEX, REINDEX : voir 34.13. Les index plein texte et vectoriels sont des extensions de MIRAJ (34.12).

34.7 Vues#

CREATE [OR REPLACE] VIEW [schéma.]vue [(colonne [, …])] AS requête
    [WITH [CASCADED | LOCAL] CHECK OPTION];
ALTER VIEW vue AS requête;
DROP VIEW [IF EXISTS] vue [, …] [CASCADE | RESTRICT];

CREATE VIEW v_commande AS
SELECT c.id, cl.nom AS client, c.passee_le, sum(l.montant) AS total
FROM commande c
JOIN client cl ON cl.id = c.client_id
LEFT JOIN ligne_commande l ON l.commande_id = c.id
GROUP BY c.id, cl.nom, c.passee_le;

SELECT * FROM v_commande;

 id | client | passee_le  | total
----+--------+------------+-------
 1  | Dupont | 2026-10-11 | 21.40

Une vue est une requête nommée, relue à chaque lecture. CREATE VIEW d'un nom déjà pris (table ou vue) rend 42P07 ; CREATE OR REPLACE VIEW remplace la définition, sans la contrainte de PostgreSQL sur les colonnes (la nouvelle requête peut en avoir d'autres). ALTER VIEW vue AS requête fait de même.

• Une liste de colonnes renomme celles de la requête : CREATE VIEW v (numero, libelle_article) AS SELECT id, libelle FROM article.
• Une colonne calculée sans alias porte le texte de son expression : la vue SELECT prix_ht * 2, upper(ref) FROM article a pour colonnes prix_ht * 2 et upper(ref) (PostgreSQL les nomme ?column? et upper). Donner un alias à chaque expression.
• SELECT * dans une vue suit la table : une colonne ajoutée ensuite à la table apparaît dans la vue (PostgreSQL fige la liste à la création).
• Une vue peut lire une table d'un autre schéma (ventes.client), une autre vue, et contenir une expression WITH ordinaire.

Écrire au travers d'une vue#

Une vue sur une table, sans agrégat ni DISTINCT, accepte INSERT, UPDATE et DELETE ; information_schema.views.is_updatable l'indique. WITH CHECK OPTION refuse une écriture dont la ligne sortirait de la vue :

CREATE VIEW v_stock AS
SELECT id, ref, libelle, prix_ht, stock FROM article WHERE stock > 0
WITH CHECK OPTION;

INSERT INTO v_stock (ref, libelle, prix_ht, stock) VALUES ('GOM-01', 'Gomme', 0.80, 5);   -- INSERT 0 1
INSERT INTO v_stock (ref, libelle, prix_ht, stock) VALUES ('REG-01', 'Règle', 2.00, 0);
-- ERROR 44000: CHECK OPTION failed "ventes"."v_stock"

WITH CHECK OPTION vaut WITH CASCADED CHECK OPTION. Une vue de jointure accepte un UPDATE qui ne touche qu'une de ses tables. RETURNING n'est pas disponible au travers d'une vue (voir 35. Langage DML).

Relire une vue#

SHOW CREATE VIEW v_stock;
SELECT pg_get_viewdef('v_stock'::regclass);
SELECT table_name, check_option, is_updatable FROM information_schema.views WHERE table_schema = 'ventes';

CREATE VIEW "v_stock" AS select "id", "ref", "libelle", "prix_ht", "stock" from "article" where ("stock" > 0) WITH CASCADED CHECK OPTION

La définition est gardée sous une forme canonique : identifiants entre guillemets, mots-clés en minuscules, parenthèses explicites ; une expression WITH y devient une table dérivée. pg_views et information_schema.views la rendent aussi. La vue garde les règles du langage PostgreSQL même si l'instance est plus tard rouverte en langage MIRAJ.

DROP VIEW#

DROP VIEW d'une vue absente rend 42P01 (un avis avec IF EXISTS) ; plusieurs vues peuvent être supprimées en une instruction. Une vue lue par une autre vue ne se supprime qu'avec CASCADE, qui supprime aussi la seconde :

ERROR 2BP01: cannot drop view v_un because other objects depend on it (view v_deux depends on view v_un); use DROP ... CASCADE to drop the dependent objects too

DROP VIEW du nom d'une table est refusé (42809, "pasvue" is not a view).

Vues en cache#

CREATE CACHED VIEW est une extension de MIRAJ : le résultat de la vue est gardé et réutilisé tant que les tables lues n'ont pas changé ; REFRESH VIEW vue le recalcule.

CREATE CACHED VIEW v_nb_articles AS SELECT count(*) AS n FROM article;
REFRESH VIEW v_nb_articles;

Une vue qui lit le catalogue (pg_catalog, information_schema) ne garde jamais de résultat, même créée ainsi : sa requête est rejouée à chaque lecture (voir le chapitre 41).

Elle tient lieu de vue matérialisée : CREATE MATERIALIZED VIEW et REFRESH MATERIALIZED VIEW n'existent pas. Voir le chapitre 7.

Ce qui n'existe pas#

CREATE TEMP VIEW, CREATE RECURSIVE VIEW, WITH RECURSIVE dans une vue (0A000), les options WITH (security_barrier), ALTER VIEW … RENAME TO. Une vue ne peut pas non plus contenir de FULL JOIN ni de LATERAL.

34.8 Séquences#

CREATE SEQUENCE [IF NOT EXISTS] nom
    [START [WITH] n] [INCREMENT [BY] n]
    [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE]
    [CYCLE | NO CYCLE];
ALTER SEQUENCE [IF EXISTS] nom [RESTART [WITH n]] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE]
    [MAXVALUE n | NO MAXVALUE] [CYCLE | NO CYCLE];
DROP SEQUENCE [IF EXISTS] nom [, …] [CASCADE | RESTRICT];

CREATE SEQUENCE num_facture;
SELECT nextval('num_facture'), nextval('num_facture'), currval('num_facture');   -- 1 | 2 | 2

CREATE SEQUENCE num_bon START 1000 INCREMENT 10;
SELECT nextval('num_bon') AS a, nextval('num_bon') AS b;                         -- 1000 | 1010

CREATE SEQUENCE num_avoir START WITH 5 INCREMENT BY 5 MINVALUE 5 MAXVALUE 15 CYCLE;
SELECT nextval('num_avoir') AS a, nextval('num_avoir') AS b,
       nextval('num_avoir') AS c, nextval('num_avoir') AS d;                     -- 5 | 10 | 15 | 5

Le nom peut être qualifié (nextval('ventes.num_facture')) ou converti (nextval('num_facture'::regclass)). Un pas négatif donne une séquence décroissante. Sans CYCLE, une séquence arrivée à sa borne est épuisée :

CREATE SEQUENCE petit MAXVALUE 2;
SELECT nextval('petit'), nextval('petit');     -- 1 | 2
SELECT nextval('petit');
-- ERROR 2200H: Sequence 'ventes.petit' has run out

Séquence comme valeur par défaut#

CREATE TABLE facture (
    numero     bigint PRIMARY KEY DEFAULT nextval('num_facture'),
    client_id  integer NOT NULL REFERENCES client (id),
    total      numeric(12,2) NOT NULL
);
INSERT INTO facture (client_id, total) VALUES (1, 21.40), (2, 5.00);

C'est la manière d'obtenir une numérotation réglable (départ, pas, cycle) ou commune à plusieurs tables ; une colonne serial ou d'identité, elle, n'a pas de séquence (voir 33.15). Une séquence encore citée par une valeur par défaut ne se supprime pas seule :

CREATE TABLE bon (id integer DEFAULT nextval('num_bon'), objet text);
DROP SEQUENCE num_bon;          -- 2BP01 : cannot drop sequence num_bon because other objects depend on it
                                --         (default value for column id of table bon depends on sequence num_bon)
DROP SEQUENCE num_bon CASCADE;  -- la séquence part, et le DEFAULT de bon.id avec elle

Avec CASCADE, la valeur par défaut des colonnes qui citent la séquence est retirée (dans tous les schémas) : la colonne reste, sans défaut. Une colonne serial ou GENERATED … AS IDENTITY n'a pas de séquence dans MIRAJ (son compteur est celui de la table) : il n'y a pas de …_id_seq à supprimer (42P01), et DROP TABLE suffit.

Fonction comme valeur par défaut#

Une fonction stockée peut aussi donner la valeur par défaut (DEFAULT ma_fonction(), DEFAULT (seuil() + 1)) : elle est exécutée pour chaque ligne écrite qui ne fournit pas la colonne (INSERT, COPY, UPDATE … SET c = DEFAULT, lignes existantes d'un ALTER TABLE … ADD COLUMN), en lecture seule, ses tables lues verrouillées avec l'instruction ; elle ne se supprime pas seule (DROP FUNCTION : 2BP01 sans CASCADE). Détails en 40.1.

Modifier et relire#

ALTER SEQUENCE num_bon RESTART WITH 5000;     -- le prochain nextval rend 5000
ALTER SEQUENCE num_bon RESTART;               -- repart de la valeur de départ (1000)
ALTER SEQUENCE num_bon INCREMENT BY 100;      -- dès le nextval suivant : dernière valeur + 100
SHOW CREATE SEQUENCE num_avoir;

CREATE SEQUENCE "num_avoir" start with 5 minvalue 5 maxvalue 15 increment by 5 cache 1000 cycle

Chaque option d'ALTER SEQUENCE vaut dès le nextval suivant, comme dans PostgreSQL : après INCREMENT BY n, il rend la dernière valeur servie plus n (la première valeur d'une séquence jamais servie reste sa valeur de départ) ; une borne abaissée sous la valeur suivante épuise la séquence (2200H) ou la fait reboucler avec CYCLE.

Les séquences se lisent dans pg_class (relkind = 'S'), pg_sequence, pg_sequences et information_schema.sequences :

SELECT sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
FROM pg_sequences WHERE sequencename = 'num_avoir';

 sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
--------------+-------------+-----------+-----------+--------------+-------+------------+------------
 num_avoir    | 5           | 5         | 15        | 5            | t     | 1000       | 5

Une séquence partage l'espace de noms des tables : un nom de table est refusé (42P07).

À connaître :

• Les valeurs ne sont pas rendues par un ROLLBACK, et une séquence réserve ses valeurs par lots (cache 1000) : des trous dans la numérotation sont normaux. Après un arrêt du serveur, la séquence reprend au lot suivant (une séquence qui avait rendu 1 et 2 rend ensuite 1001). Une colonne serial, elle, reprend à la suite de la plus grande valeur présente.
• La valeur maximale par défaut est 9 223 372 036 854 775 806.
• CACHE n est accepté. Options non reconnues (42601) : AS type, OWNED BY, CREATE TEMP SEQUENCE, ALTER SEQUENCE … RENAME TO.
• DROP SEQUENCE d'une séquence absente rend 42P01 (Unknown SEQUENCE: 'ventes.jamais'), un avis avec IF EXISTS. Une séquence épuisée rend 2200H.

34.9 Commentaires#

COMMENT ON TABLE client IS 'Les clients';
COMMENT ON COLUMN client.nom IS 'Raison sociale ou nom complet';

SELECT obj_description('client'::regclass, 'pg_class') AS sur_table,
       col_description('client'::regclass, 3)          AS sur_colonne;

 sur_table   | sur_colonne
-------------+-------------------------------
 Les clients | Raison sociale ou nom complet

IS NULL retire le commentaire. Les commentaires se lisent par obj_description, col_description, dans pg_description, et SHOW CREATE TABLE les écrit à la suite de la table. Un commentaire de colonne réécrit la table : sur une grande table, l'instruction prend le temps d'une modification de colonne.

COMMENT ON ne porte que sur une table ou une colonne ; pour un index, une vue, une séquence, un schéma ou une contrainte :

ERROR 0A000: COMMENT ON an object other than a table or a column is not supported yet in the PostgreSQL language

34.10 Relire les définitions#

SHOW CREATE TABLE commande;

CREATE TABLE "commande" (
  "id" bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 2) NOT NULL,
  "client_id" integer NOT NULL,
  "passee_le" date NOT NULL DEFAULT CURRENT_DATE,
  "statut" character varying(12) NOT NULL DEFAULT 'nouvelle'::character varying,
  PRIMARY KEY ("id"),
  CONSTRAINT "commande_ibfk_1" FOREIGN KEY ("client_id") REFERENCES "client" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT "commande_statut_ck" CHECK (("statut" in ('nouvelle','expediee','annulee')))
);
CREATE INDEX "commande_client_idx" ON "commande" ("client_id");
CREATE INDEX "commande_date_statut_idx" ON "commande" ("passee_le", "statut")

Le texte rendu se rejoue tel quel pour recréer la table. Il diffère de ce qui a été écrit : serial y devient GENERATED BY DEFAULT AS IDENTITY (START WITH n), n étant le prochain numéro du compteur, now() devient CURRENT_TIMESTAMP, une constante est convertie au type de la colonne ('nouvelle'::character varying), une colonne sans valeur par défaut porte DEFAULT NULL, et les contraintes de colonne sont regroupées en fin de table.

La clé primaire porte le nom <table>_pkey dans information_schema.table_constraints, pg_constraint et pg_indexes. Le catalogue est décrit au chapitre 41. Catalogue et outils.

34.11 DDL et transactions#

Dans un bloc de transaction, les instructions de définition courantes font partie de la transaction : ROLLBACK les défait avec les écritures, comme dans PostgreSQL.

BEGIN;
INSERT INTO article (ref, libelle, prix_ht) VALUES ('TX-01', 'Avant DDL', 1);
CREATE TABLE essai (a integer);
ROLLBACK;

SELECT count(*) FROM article WHERE ref = 'TX-01';   -- 0 : la ligne est annulée
SELECT count(*) FROM essai;                         -- ERROR 42P01 : la table n'existe pas

ROLLBACK TO SAVEPOINT défait de même les définitions faites depuis le point de sauvegarde, et la fermeture de la session sans COMMIT les annule :

BEGIN;
CREATE TABLE e3 (a integer);
SAVEPOINT s1;
CREATE TABLE e4 (a integer);
ROLLBACK TO SAVEPOINT s1;
COMMIT;                                              -- e3 existe, e4 non

Une instruction de la dernière ligne du tableau valide tout ce qui la précède dans le bloc, y compris les définitions annulables :

BEGIN;
INSERT INTO tr (a) VALUES (50);
CREATE VIEW vtx AS SELECT 1 AS a;
ROLLBACK;
SELECT count(*) FROM tr WHERE a = 50;               -- 1 : la ligne est restée

Un script de migration qui veut pouvoir tout défaire s'en tient donc aux instructions annulables dans son bloc, ou prévoit les instructions inverses ou une sauvegarde (voir 19. Sauvegarde et restauration).

Chaque instruction est en outre atomique à elle seule : un ALTER TABLE qui échoue (conversion impossible, contrainte violée) laisse la table inchangée.

34.12 Extensions de MIRAJ#

Les extensions de définition de MIRAJ restent disponibles en langage PostgreSQL, écrites avec ses conventions (identifiants entre guillemets doubles, chaînes standard). Elles sont décrites au chapitre 7 ; en voici la forme.

Index plein texte (chapitre 7) :

CREATE FULLTEXT INDEX article_ft ON article (libelle);
SELECT ref FROM article WHERE MATCH (libelle) AGAINST ('Stylo');      -- STY-01

Index vectoriel : la colonne doit être NOT NULL, et une table n'en porte qu'un.

CREATE TABLE article_vecteur (article_id integer PRIMARY KEY, e vector(3) NOT NULL);
CREATE INDEX article_vecteur_hnsw ON article_vecteur USING hnsw (e vector_l2_ops);

La forme de MIRAJ CREATE VECTOR INDEX nom ON table (colonne) est équivalente. Avec USING hnsw, la classe d'opérateurs choisit la distance : vector_l2_ops (euclidienne), vector_cosine_ops (cosinus) ou vector_ip_ops (produit scalaire). Sans classe (USING hnsw (e)), l'index prend vector_l2_ops. pg_get_indexdef et pg_indexes.indexdef écrivent toujours la classe, et WITH (m = …) quand m a été choisi (USING hnsw (e vector_cosine_ops) WITH (m = 8)) : la définition rejouée recrée le même index, de même distance. ef_construction, accepté sans effet, n'y figure pas.

Partitionnement : il s'écrit dans la syntaxe de MIRAJ, avec la liste des partitions ; la forme déclarative de PostgreSQL (PARTITION BY RANGE (colonne) seul, puis CREATE TABLE … PARTITION OF) n'est pas reconnue.

CREATE TABLE mesure_annuelle (
    id     integer NOT NULL,
    jour   date NOT NULL,
    valeur numeric(10,2),
    PRIMARY KEY (id, jour)
) PARTITION BY RANGE (year(jour)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

SHOW CREATE TABLE rend la clause telle que MIRAJ la relit, identifiants entre guillemets doubles (PARTITION BY RANGE (year("jour")), suivie de la liste des partitions) : le texte affiché recrée la même table partitionnée.

Les partitions se lisent dans information_schema.partitions. Ce que le partitionnement apporte dépend de l'édition : voir le chapitre 7.

Extensions de PostgreSQL : CREATE EXTENSION [IF NOT EXISTS] nom et DROP EXTENSION nom rendent CREATE et DROP sans rien changer pour plpgsql, pgcrypto, uuid-ossp, unaccent, pg_trgm, fuzzystrmatch, btree_gin, btree_gist et vector : leurs fonctions (gen_random_uuid, uuid_generate_v4, digest, crypt, unaccent, similarity, levenshtein…) et le type vector sont toujours disponibles (voir 37. Fonctions SQL). Un script qui commence par CREATE EXTENSION IF NOT EXISTS pgcrypto; ou CREATE EXTENSION IF NOT EXISTS vector; passe donc tel quel. Une autre extension (postgis…) est refusée (0A000).

Autres : vues en cache (34.7), colonnes enum(…) et set(…) (33.11), CREATE OR REPLACE TABLE. Les autres extensions de MIRAJ sont listées au chapitre 30 du manuel général. Les routines et les déclencheurs s'écrivent en PL/pgSQL : voir 40. Routines et déclencheurs.

34.13 Non pris en charge et écarts#

Instructions absentes#

Écarts de comportement#

Points à surveiller#

Les comportements suivants ont été constatés ; ils demandent de la vigilance dans un script.

• DROP TABLE … CASCADE refusé à cause d'une clé étrangère : l'erreur XX000 est rendue et rien n'est supprimé, ni la table ni les vues qui la lisent.
• DROP INDEX d'un nom porté par deux tables au même titre (index créés en langage MIRAJ, ou colonnes UNIQUE de même nom sans index au nom écrit) : refusé (42710) ; écrire DROP INDEX nom ON table.
• CREATE SCHEMA s suivi d'un CREATE FUNCTION : refusé (0A000) ; seuls les tables, vues, index, séquences, déclencheurs et GRANT sont des éléments de schéma. La fonction d'un déclencheur écrit en élément de schéma vient donc d'un autre schéma : son nom s'écrit qualifié (EXECUTE FUNCTION outils.f()).
• Contrainte nommée déjà prise : CREATE TABLE t (…, CONSTRAINT nom UNIQUE (…)) est refusé (42P07) si nom est déjà celui d'une table, d'une vue, d'une séquence ou d'un index au nom écrit d'une autre table du schéma, comme CREATE INDEX.
• ALTER SEQUENCE … MINVALUE | MAXVALUE qui laisserait dehors la valeur courante de la séquence : refusé (22023, RESTART value (10) cannot be greater than MAXVALUE (7)) ; ajouter RESTART [WITH n].
• DROP VIEW du nom d'une table lue par des vues : le refus est rendu avec le message d'une dépendance (cannot drop view article because other objects depend on it) ; la table n'est pas supprimée.
• DROP SEQUENCE d'une séquence citée par un DEFAULT nextval(…) : refusé (2BP01) sans CASCADE, qui retire ces valeurs par défaut ; de même DROP FUNCTION d'une fonction appelée par une valeur par défaut.
• Fonction en valeur par défaut : exécutée en lecture seule ; une fonction qui écrit fait échouer l'écriture de la ligne (0A000).
• SQLSTATE XX000 : quelques refus de définition propres à MIRAJ (ON DELETE SET DEFAULT, expression non déterministe d'un CHECK, table encore référencée) portent ce code générique ; se fier au message. Les refus ordinaires d'une clé étrangère (42P01, 42703, 42830, 42804) et la séquence épuisée (2200H) portent le code de PostgreSQL.

Voir aussi#

• 33. Types de données : types, valeurs par défaut, identité, colonnes générées.
• 35. Langage DML : INSERT, UPDATE, DELETE, ON CONFLICT, COPY.
• 38. Transactions et session : search_path, transactions.
• 39. Comptes et privilèges : droits sur les schémas et les tables.
• 40. Routines et déclencheurs : CREATE FUNCTION, CREATE TRIGGER.
• 41. Catalogue et outils : pg_catalog, information_schema, psql.
• Chapitre 30 et chapitre 7.