34. PostgreSQL: Data definition language (DDL)

This chapter describes the statements that create, alter and drop the schemas, tables, constraints, indexes, views and sequences of a MIRAJ instance in the PostgreSQL language. Column types, default values, identity columns and generated columns are described in chapter 33. Data types.

The examples use a sales database, created in 34.2.

34.1 Schemas#

One MIRAJ database per schema#

An instance in the PostgreSQL language presents a single PostgreSQL database, named miraj; each MIRAJ database is a schema in it. Creating a schema therefore creates a MIRAJ database, with its own tables, and dropping it drops all its contents.

CREATE SCHEMA [IF NOT EXISTS] name [AUTHORIZATION account] [element …];
CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION account [element …];
DROP SCHEMA [IF EXISTS] name [CASCADE | RESTRICT];

CREATE SCHEMA ventes;
CREATE SCHEMA IF NOT EXISTS ventes;       -- notice, no error
SELECT nspname FROM pg_namespace ORDER BY nspname;

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

pg_catalog and information_schema are the catalog schemas; miraj is the system database (accounts and privileges), visible only to the root account. CREATE DATABASE name and DROP DATABASE name are accepted as synonyms of CREATE SCHEMA and DROP SCHEMA.

AUTHORIZATION account: a schema has no owner in MIRAJ; the named account receives all privileges on the schema (GRANT ALL PRIVILEGES ON schema.* TO account), which requires the right to grant them. Nothing is granted when the account is the session's own account (AUTHORIZATION CURRENT_USER). Without a schema name (CREATE SCHEMA AUTHORIZATION app), the schema takes the account's name. An unknown account returns 42704 (role "…" does not exist) and the schema is not created. Other privileges are given with GRANT (see 39. Accounts and privileges).

Schema elements: as in PostgreSQL, CREATE SCHEMA can create the objects of the schema in one go, written one after the other without ;:

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;

The accepted elements are CREATE TABLE, CREATE [OR REPLACE] VIEW, CREATE [UNIQUE] INDEX, CREATE SEQUENCE, CREATE TRIGGER (its function, which comes from another schema, is written qualified: EXECUTE FUNCTION outils.f()) and GRANT. They are created in the schema, in the order written; the session's current schema does not change. It is all or nothing: if an element fails, the schema that was just created is removed together with what it already contained, and the element's error is returned.

CREATE SCHEMA public returns 42P06 (schema "public" already exists): public is the name of the current schema (see below), so it always exists and no database of that name is created. CREATE SCHEMA IF NOT EXISTS public has no effect (a notice).

DROP SCHEMA always drops the schema and all its contents, with CASCADE, with RESTRICT or with neither: RESTRICT does not protect a schema that contains tables, whereas PostgreSQL refuses.

A table name has at most 64 characters. A column name accepts non-ASCII letters and digits (prénom, "Âge", 64 characters at most): written without quotes, a name that contains a non-ASCII letter is folded to lowercase as in PostgreSQL (PRÉNOM and Prénom name prénom); in quotes it is kept as is, and its case matters for its non-ASCII letters ("Âge" is not âge). An index name accepts them too. A table, view, sequence, schema or routine name, however, does not accept accented letters, even in quotes: CREATE TABLE "événement" (…) returns 42000 (Incorrect table name 'événement'). Combining marks (an accented letter in decomposed form) and punctuation are refused everywhere. Spaces and uppercase letters are accepted in quotes ("Table Mixte"), the name remaining case-insensitive for its ASCII letters.

Current schema and search_path#

The current schema is the one where tables written without qualification are looked up and created. It is set by the database name given at connection, when it designates a schema, and then by 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 keeps the first schema in the list that exists; the following ones are not consulted. If none exists, the current schema is kept, without error.
• SHOW search_path returns the current schema (public as long as none is chosen).
• USE ventes is accepted and does the same.
• Without a current schema, creating an unqualified table fails (3F000, no schema has been selected to create in); this is also the case after the current schema is dropped.

public is not a schema: in a name, public. designates the current schema. public.client and client are the same table, and CREATE TABLE public.t (…) creates t in the current schema.

A table in another schema is named schema.table (ventes.client). The three-part form miraj.ventes.client is not recognized (42601).

Session variables and the search path are detailed in chapter 38. Transactions and session.

What does not exist#

ALTER SCHEMA … RENAME TO and ALTER SCHEMA … OWNER TO are refused (0A000, This version of Miraj doesn't yet support 'ALTER').

34.2 CREATE TABLE#

Syntax#

CREATE [TEMP | TEMPORARY | UNLOGGED] TABLE [IF NOT EXISTS] [schema.]table (
    column_definition [, …]
    [, [CONSTRAINT name] PRIMARY KEY (column [, …])]
    [, [CONSTRAINT name] UNIQUE (column [, …])]
    [, [CONSTRAINT name] CHECK (condition)]
    [, [CONSTRAINT name] FOREIGN KEY (column [, …])
           REFERENCES ref_table [(column [, …])]
           [ON DELETE action] [ON UPDATE action]]
);

where column_definition is written:

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

action is NO ACTION, RESTRICT, CASCADE or SET NULL.

The example database#

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

Creation errors#

With IF NOT EXISTS, an existing table is left as is: the statement returns CREATE TABLE and a notice (NOTICE: relation "client" already exists, skipping).

Temporary tables#

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

TEMP or TEMPORARY creates a table private to the session: other sessions do not see it (42P01), and it disappears when the connection closes. It appears neither in SHOW TABLES nor in pg_tables. A temporary table hides a permanent table of the same name as long as it exists; DROP TABLE then drops the temporary table.

ON COMMIT PRESERVE ROWS (the default) keeps the rows after COMMIT. With ON COMMIT DELETE ROWS, the temporary table is emptied at every commit, including, outside a block, at the end of every statement, which is its own transaction; its identity counter continues. This clause is accepted only on a temporary table (0A000 otherwise).

With ON COMMIT DROP, the temporary table is dropped at the end of the transaction in which it was created, committed or rolled back:

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;        -- ERROR 42P01: the table no longer exists

Outside a block, the statement is its own transaction: the table does not outlive it. A definition statement that commits the transaction by itself (see 34.11) drops it too. Like ON COMMIT DELETE ROWS, the clause is accepted only on a temporary table (0A000 otherwise).

CREATE UNLOGGED TABLE creates an ordinary table.

CREATE TABLE … (LIKE …)#

CREATE TABLE article_modele (LIKE article);

Creates an empty table with the same structure. The copy is complete: columns, default values, identity, primary key, unique constraints and CHECK constraints are carried over; the INCLUDING … / EXCLUDING … options are accepted with no effect. LIKE must be the only element of the list ((LIKE article, extra integer): 42601).

CREATE TABLE … AS#

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

Creates the table from the result of a query and copies the rows into it; the statement returns SELECT n, where n is the number of rows copied. The columns take the type and NOT NULL of the query, without primary key, identity, default value or constraint:

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 leaves an existing table untouched (SELECT 0 and a notice). The forms WITH NO DATA, CREATE TABLE t (a, b) AS …, CREATE TABLE t AS TABLE u and SELECT … INTO t are not supported (see 34.13).

Storage clauses#

TABLESPACE name and INHERITS (table) are accepted with no effect: the table created by CREATE TABLE t (a integer) INHERITS (article) has only the column a and inherits nothing. WITH (fillfactor = …) is a syntax error. Partitioning is written in MIRAJ syntax (see 34.12).

34.3 Constraints#

Overview#

CONSTRAINT name before a UNIQUE, CHECK or foreign key constraint sets its name. The default names are those of MIRAJ, not those of PostgreSQL (client_code_key, client_plafond_check, commande_client_id_fkey): name explicitly the constraints you intend to alter or to cite in an 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#

A NOT NULL column refuses NULL (23502, Column 'a' cannot be null); omitting it in an INSERT when it has no default value is the same error (Field 'a' doesn't have a default value). An explicit NULL allows NULL, which is the default. A name given to a NOT NULL constraint (CONSTRAINT x NOT NULL) is accepted and forgotten.

Primary key#

A table has at most one primary key, on one or more columns; its columns become NOT NULL. It is always named <table>_pkey in the catalog: a name given by CONSTRAINT name PRIMARY KEY is ignored. A duplicate is refused:

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

Uniqueness#

UNIQUE refuses two rows with the same values; several rows may hold NULL. On a text column, values are compared byte by byte: 'c001' is not a duplicate of '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'

Declare the column COLLATE utf8mb4_general_ci for a uniqueness that ignores case and accents (see 33.5).

CHECK#

The condition is evaluated on every write of the row; it is satisfied if it is true or NULL. A constraint written on a column may cite the other columns of the row.

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

The condition accepts neither a subquery nor a function whose result changes: CHECK (a > (SELECT 1)) and CHECK (a < now()) are refused (XX000, An expression of a check constraint 'x' contains disallowed function., followed by the function name for now()). It keeps the rules of the PostgreSQL language (~ operator, length in characters) even if the instance is later reopened in the MIRAJ language.

Foreign keys#

column type REFERENCES ref_table [(column)] [ON DELETE action] [ON UPDATE action]
[CONSTRAINT name] FOREIGN KEY (column [, …]) REFERENCES ref_table [(column [, …])]
    [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 becomes 50
DELETE FROM commande WHERE id = 1;           -- ON DELETE CASCADE: its lines are deleted

Rules:

• REFERENCES table without a column designates the primary key of the referenced table (42830, there is no primary key for referenced table "sanspk", if it has none).
• The referenced columns must carry the primary key or a UNIQUE constraint.
• The columns on both sides must have compatible types.
• A foreign key may reference its own table (tree structure), and may span several columns.
• A NULL child column is not checked.
• The referenced table must be in the same schema: a reference to autre_schema.table is refused (0A000, This version of Miraj doesn't yet support 'FOREIGN KEY referencing another database').

DEFERRABLE, INITIALLY DEFERRED, INITIALLY IMMEDIATE and MATCH FULL are accepted with no effect: a foreign key is always checked at the statement, never at the end of the transaction. Insert the referenced row before the one that cites it.

34.4 ALTER TABLE#

ALTER TABLE [ONLY] [schema.]table [*] action [, action …];

ONLY (the one pg_dump writes: ALTER TABLE ONLY ventes.client ADD CONSTRAINT …) and * are accepted with no effect: MIRAJ has no table inheritance.

Several actions separated by commas apply in one statement:

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

Columns#

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;

Adding. Existing rows receive the new column's default value, NULL without DEFAULT. A NOT NULL column added without a default value to a table that contains rows is refused, as in PostgreSQL, and the table is left unchanged:

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

ADD COLUMN IF NOT EXISTS of an existing column and DROP COLUMN IF EXISTS of a missing column return a notice (column "pays" of relation "client" already exists, skipping).

Changing the type. The values are converted; without USING, the type's usual conversion applies (a text made of digits becomes an integer). USING accepts only the column itself, converted or not (USING telephone, USING telephone::integer); any other expression is refused:

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

A value that cannot be converted makes the statement fail, and the table is left unchanged:

ALTER COLUMN … TYPE jsonb (or json) on a JSON column changes only the declared type.

Dropping. DROP COLUMN also removes the unique constraints and indexes that covered only that column. It is refused (XX000), even with CASCADE, as long as the column is cited elsewhere:

Drop the constraint or the generated column first; an index on an expression that cites the column counts as a generated column. A view that cites the column does not prevent its removal: it is the view's next read that fails (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).

Constraints#

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;

An added constraint is checked immediately against the existing rows; if one violates it, the statement fails and nothing is added (23505 for a uniqueness or primary key constraint, 23514 for a CHECK). NOT VALID and VALIDATE CONSTRAINT are not supported.

To change a constraint, drop it and recreate it in the same statement:

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

DROP CONSTRAINT of an unknown name returns 42704 (constraint "absente" of relation "commande" does not exist), a simple notice with IF EXISTS. The primary key is dropped under the name <table>_pkey; that of an identity column (serial) cannot be dropped (42000), because such a column must carry a key.

Renaming the table#

ALTER TABLE client RENAME TO clients;

The foreign keys of other tables follow the new name. Renaming to a name already in use returns 42P07. The primary key's name in the catalog follows the table (clients_pkey).

MIRAJ forms#

The MIRAJ-specific forms remain accepted: ADD COLUMN c type FIRST or AFTER other (position of the column), MODIFY column type …, CHANGE old new type …, DROP INDEX name, as well as partition management. They are described in chapter 7.

Refused forms#

ALTER TABLE IF EXISTS, RENAME old TO new without the word COLUMN, RENAME CONSTRAINT, SET SCHEMA, OWNER TO, ALTER COLUMN … SET STATISTICS, ENABLE ROW LEVEL SECURITY, VALIDATE CONSTRAINT and ADD CONSTRAINT … NOT VALID return 0A000 (This version of Miraj doesn't yet support 'ALTER TABLE'). ALTER COLUMN … ADD GENERATED … AS IDENTITY, DROP IDENTITY and DROP EXPRESSION are syntax errors (42601).

34.5 DROP TABLE and TRUNCATE#

DROP TABLE#

DROP TABLE [IF EXISTS] [schema.]table [, …] [CASCADE | RESTRICT];

Drops the table, its rows, its indexes and its constraints. A list of tables is dropped entirely or not at all: if one is missing (42P01), none is dropped.

CASCADE also drops the views that read the table. Unlike PostgreSQL, it does not remove the foreign keys of the other tables: drop the child table first, or its constraint (ALTER TABLE commande DROP CONSTRAINT commande_ibfk_1).

TRUNCATE#

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

Empties the table; the statement returns the tag TRUNCATE TABLE. As in PostgreSQL, the counter of an identity column continues with no option or with CONTINUE IDENTITY, and restarts from its start value with 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;                 -- also empties ligne_commande

• A table referenced by a foreign key can be emptied only with CASCADE, which also empties, step by step, the tables that reference it.
• One table per statement: TRUNCATE a, b returns 0A000 (TRUNCATE of several tables is not supported yet in the PostgreSQL language).
• In a transaction block, TRUNCATE is undone by ROLLBACK, as in PostgreSQL (see 35.6 and 34.11).

34.6 Indexes#

CREATE [UNIQUE] INDEX [IF NOT EXISTS] name ON [schema.]table [USING btree] (element [, …]);
DROP INDEX [IF EXISTS] [schema.]name [CASCADE | RESTRICT];

where element is a column or an expression in parentheses.

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)

• The name is mandatory: CREATE INDEX ON commande (passee_le) is a syntax error. It is not qualified by a schema; the index lives in its table's schema.
• A name already taken on the table returns 42P07 (Duplicate key name 'commande_client_idx'), a notice with IF NOT EXISTS. Two tables may have an index with the same name.
• CREATE UNIQUE INDEX refuses duplicates like a UNIQUE constraint; it fails (23505) if the table already contains some.
• The primary key and each UNIQUE constraint have their index, visible in pg_indexes (<table>_pkey, constraint name).
• ASC, DESC, NULLS FIRST and NULLS LAST after a column ((nom DESC NULLS LAST)) are accepted with no effect: the index is always ascending, and the order of a result remains the one the query asks for. USING hash creates an ordinary index (btree).
• The index is built during the statement; CONCURRENTLY is not recognized.

Index on an expression#

An element in parentheses is an expression: ((lower(libelle))), ((doc ->> 'ville')), ((libelle || '-' || ref)), alone or mixed with columns ((stock, (lower(ref)))). The index serves a condition expression = constant written with the same 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

• The expression must be deterministic: ((libelle || now())) is refused (XX000).
• The index serves neither a range (lower(libelle) > 'a'), nor an IN, nor an ORDER BY on the expression: EXPLAIN then shows a full scan (type = ALL).
• CREATE UNIQUE INDEX on an expression is refused (0A000, UNIQUE index on an expression is not supported yet in the PostgreSQL language).
• After an expression, an operator class or NULLS FIRST | LAST are accepted with no effect; after a plain column, NULLS FIRST | LAST likewise, but an operator class is a syntax error.
• A column cited by the expression cannot be dropped as long as the index exists.

EXPLAIN returns the MIRAJ plan (see 36. SELECT queries).

DROP INDEX#

DROP INDEX commande_client_idx;

DROP INDEX does not name the table, as in PostgreSQL: the index is looked up among the tables of the current schema, or of the schema written (DROP INDEX ventes.commande_client_idx).

An index name is unique in the schema, as in PostgreSQL where an index is a relation: CREATE INDEX name (or ALTER TABLE … ADD CONSTRAINT name UNIQUE) is refused with 42P07 (relation "name" already exists) when that name is already the name of an index of another table of the schema, of a table, a view or a sequence; CREATE INDEX IF NOT EXISTS then does nothing. Temporary tables have their own namespace.

Only written names count. The implicit index of a UNIQUE column with no constraint name bears the column's name in MIRAJ (PostgreSQL would call it client_email_key): it does not take that name in the schema, and CREATE INDEX email ON contact (email) succeeds even though another table has an email UNIQUE column. Since MIRAJ does not keep the origin of a name, a uniqueness index that bears the name of its first column (followed or not by _2, _3…) is considered implicit; a CREATE UNIQUE INDEX email ON client (email) written that way is therefore treated the same. On the very table that holds the implicit index, the name remains taken (an index name is unique in its table).

DROP INDEX name targets the index with the written name when implicit indexes bear the same name. Two tables can nevertheless hold an index of the same name on an equal footing: indexes created in the MIRAJ language (where the name is unique only in its table), or UNIQUE columns of the same name in two tables with no index under a written name. DROP INDEX name is then refused rather than dropping one at random; write the table:

DROP INDEX email;             -- 42710: index name "email" is ambiguous: tables "client" and "contact" both have …
DROP INDEX email ON contact;  -- MIRAJ form, accepted: the one on this table

An index created on the child column of a foreign key (CREATE INDEX … ON commande (client_id)) is dropped like any other: like PostgreSQL, MIRAJ does not require an index on that column in the PostgreSQL language. The foreign key is still checked on every write; without the index, deleting or modifying a parent row reads the whole child table.

DROP INDEX of the name of a UNIQUE constraint drops the constraint (PostgreSQL requires DROP CONSTRAINT).

What does not exist#

Partial indexes (WHERE), included columns (INCLUDE), USING gin, USING gist and USING brin, CONCURRENTLY, ALTER INDEX, REINDEX: see 34.13. Full-text and vector indexes are MIRAJ extensions (34.12).

34.7 Views#

CREATE [OR REPLACE] VIEW [schema.]view [(column [, …])] AS query
    [WITH [CASCADED | LOCAL] CHECK OPTION];
ALTER VIEW view AS query;
DROP VIEW [IF EXISTS] view [, …] [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

A view is a named query, re-evaluated on every read. CREATE VIEW of a name already taken (table or view) returns 42P07; CREATE OR REPLACE VIEW replaces the definition, without the PostgreSQL constraint on columns (the new query may have different ones). ALTER VIEW view AS query does the same.

• A column list renames those of the query: CREATE VIEW v (numero, libelle_article) AS SELECT id, libelle FROM article.
• A computed column without an alias bears the text of its expression: the view SELECT prix_ht * 2, upper(ref) FROM article has the columns prix_ht * 2 and upper(ref) (PostgreSQL names them ?column? and upper). Give each expression an alias.
• SELECT * in a view follows the table: a column added later to the table appears in the view (PostgreSQL freezes the list at creation).
• A view may read a table of another schema (ventes.client), another view, and contain an ordinary WITH expression.

Writing through a view#

A view on one table, without aggregate or DISTINCT, accepts INSERT, UPDATE and DELETE; information_schema.views.is_updatable says so. WITH CHECK OPTION refuses a write whose row would leave the view:

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 means WITH CASCADED CHECK OPTION. A join view accepts an UPDATE that touches only one of its tables. RETURNING is not available through a view (see 35. DML language).

Reading a view back#

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

The definition is kept in a canonical form: quoted identifiers, lowercase keywords, explicit parentheses; a WITH expression becomes a derived table there. pg_views and information_schema.views return it too. The view keeps the rules of the PostgreSQL language even if the instance is later reopened in the MIRAJ language.

DROP VIEW#

DROP VIEW of a missing view returns 42P01 (a notice with IF EXISTS); several views can be dropped in one statement. A view read by another view can be dropped only with CASCADE, which drops the second one too:

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 of a table's name is refused (42809, "pasvue" is not a view).

Cached views#

CREATE CACHED VIEW is a MIRAJ extension: the view's result is kept and reused as long as the tables read have not changed; REFRESH VIEW view recomputes it.

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

A view that reads the catalog (pg_catalog, information_schema) never keeps a result, even when created this way: its query is replayed on every read (see chapter 41).

It stands in for a materialized view: CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW do not exist. See chapter 7.

What does not exist#

CREATE TEMP VIEW, CREATE RECURSIVE VIEW, WITH RECURSIVE in a view (0A000), the WITH (security_barrier) options, ALTER VIEW … RENAME TO. A view cannot contain a FULL JOIN or a LATERAL either.

34.8 Sequences#

CREATE SEQUENCE [IF NOT EXISTS] name
    [START [WITH] n] [INCREMENT [BY] n]
    [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE]
    [CYCLE | NO CYCLE];
ALTER SEQUENCE [IF EXISTS] name [RESTART [WITH n]] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE]
    [MAXVALUE n | NO MAXVALUE] [CYCLE | NO CYCLE];
DROP SEQUENCE [IF EXISTS] name [, …] [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

The name may be qualified (nextval('ventes.num_facture')) or converted (nextval('num_facture'::regclass)). A negative step gives a descending sequence. Without CYCLE, a sequence that has reached its bound is exhausted:

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

Sequence as a default value#

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

This is how to get a numbering that is adjustable (start, step, cycle) or shared by several tables; a serial or identity column, by contrast, has no sequence (see 33.15). A sequence still cited by a default value cannot be dropped on its own:

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;  -- the sequence goes, and so does the DEFAULT of bon.id

With CASCADE, the default value of the columns that cite the sequence is removed (in all schemas): the column remains, without a default. A serial or GENERATED … AS IDENTITY column has no sequence in MIRAJ (its counter is the table's): there is no …_id_seq to drop (42P01), and DROP TABLE is enough.

Function as a default value#

A stored function can also provide the default value (DEFAULT ma_fonction(), DEFAULT (seuil() + 1)): it is executed for each written row that does not supply the column (INSERT, COPY, UPDATE … SET c = DEFAULT, existing rows of an ALTER TABLE … ADD COLUMN), read-only, with the tables it reads locked together with the statement; it cannot be dropped on its own (DROP FUNCTION: 2BP01 without CASCADE). Details in 40.1.

Altering and reading back#

ALTER SEQUENCE num_bon RESTART WITH 5000;     -- the next nextval returns 5000
ALTER SEQUENCE num_bon RESTART;               -- restarts from the start value (1000)
ALTER SEQUENCE num_bon INCREMENT BY 100;      -- from the next nextval: last value + 100
SHOW CREATE SEQUENCE num_avoir;

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

Each ALTER SEQUENCE option applies from the next nextval, as in PostgreSQL: after INCREMENT BY n, it returns the last value served plus n (the first value of a sequence never served remains its start value); a bound lowered below the next value exhausts the sequence (2200H) or makes it wrap around with CYCLE.

Sequences are read in pg_class (relkind = 'S'), pg_sequence, pg_sequences and 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

A sequence shares the tables' namespace: a table name is refused (42P07).

Things to know:

• Values are not given back by a ROLLBACK, and a sequence reserves its values in batches (cache 1000): gaps in the numbering are normal. After a server stop, the sequence resumes at the next batch (a sequence that had returned 1 and 2 then returns 1001). A serial column, by contrast, resumes after the largest value present.
• The default maximum value is 9,223,372,036,854,775,806.
• CACHE n is accepted. Unrecognized options (42601): AS type, OWNED BY, CREATE TEMP SEQUENCE, ALTER SEQUENCE … RENAME TO.
• DROP SEQUENCE of a missing sequence returns 42P01 (Unknown SEQUENCE: 'ventes.jamais'), a notice with IF EXISTS. An exhausted sequence returns 2200H.

34.9 Comments#

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 removes the comment. Comments are read with obj_description, col_description, in pg_description, and SHOW CREATE TABLE writes them after the table. A column comment rewrites the table: on a large table, the statement takes as long as a column change.

COMMENT ON applies only to a table or a column; for an index, a view, a sequence, a schema or a constraint:

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

34.10 Reading definitions back#

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

The text returned can be replayed as is to recreate the table. It differs from what was written: serial becomes GENERATED BY DEFAULT AS IDENTITY (START WITH n), where n is the next number of the counter, now() becomes CURRENT_TIMESTAMP, a constant is converted to the column's type ('nouvelle'::character varying), a column without a default value carries DEFAULT NULL, and the column constraints are grouped at the end of the table.

The primary key is named <table>_pkey in information_schema.table_constraints, pg_constraint and pg_indexes. The catalog is described in chapter 41. Catalog and tools.

34.11 DDL and transactions#

In a transaction block, the usual definition statements are part of the transaction: ROLLBACK undoes them along with the writes, as in 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: the row is rolled back
SELECT count(*) FROM essai;                         -- ERROR 42P01: the table does not exist

ROLLBACK TO SAVEPOINT likewise undoes the definitions made since the savepoint, and closing the session without COMMIT rolls them back:

BEGIN;
CREATE TABLE e3 (a integer);
SAVEPOINT s1;
CREATE TABLE e4 (a integer);
ROLLBACK TO SAVEPOINT s1;
COMMIT;                                              -- e3 exists, e4 does not

A statement from the last row of the table commits everything that precedes it in the block, including the definitions that can be rolled back:

BEGIN;
INSERT INTO tr (a) VALUES (50);
CREATE VIEW vtx AS SELECT 1 AS a;
ROLLBACK;
SELECT count(*) FROM tr WHERE a = 50;               -- 1: the row stayed

A migration script that wants to be able to undo everything therefore sticks to the statements that can be rolled back in its block, or plans inverse statements or a backup (see 19. Backup and restore).

Each statement is also atomic on its own: an ALTER TABLE that fails (impossible conversion, violated constraint) leaves the table unchanged.

34.12 MIRAJ extensions#

MIRAJ's definition extensions remain available in the PostgreSQL language, written with its conventions (identifiers in double quotes, standard strings). They are described in chapter 7; here is their form.

Full-text index (chapter 7):

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

Vector index: the column must be NOT NULL, and a table carries only one.

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

The MIRAJ form CREATE VECTOR INDEX name ON table (column) is equivalent. With USING hnsw, the operator class chooses the distance: vector_l2_ops (Euclidean), vector_cosine_ops (cosine) or vector_ip_ops (inner product). Without a class (USING hnsw (e)), the index takes vector_l2_ops. pg_get_indexdef and pg_indexes.indexdef always write the class, and WITH (m = …) when m was chosen (USING hnsw (e vector_cosine_ops) WITH (m = 8)): the replayed definition recreates the same index, with the same distance. ef_construction, accepted with no effect, does not appear there.

Partitioning: it is written in MIRAJ syntax, with the list of partitions; PostgreSQL's declarative form (PARTITION BY RANGE (column) alone, then CREATE TABLE … PARTITION OF) is not recognized.

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 returns the clause as MIRAJ reads it back, with identifiers in double quotes (PARTITION BY RANGE (year("jour")), followed by the list of partitions): the text displayed recreates the same partitioned table.

Partitions are read in information_schema.partitions. What partitioning brings depends on the edition: see chapter 7.

PostgreSQL extensions: CREATE EXTENSION [IF NOT EXISTS] name and DROP EXTENSION name return CREATE and DROP without changing anything for plpgsql, pgcrypto, uuid-ossp, unaccent, pg_trgm, fuzzystrmatch, btree_gin, btree_gist and vector: their functions (gen_random_uuid, uuid_generate_v4, digest, crypt, unaccent, similarity, levenshtein…) and the vector type are always available (see 37. SQL functions). A script that starts with CREATE EXTENSION IF NOT EXISTS pgcrypto; or CREATE EXTENSION IF NOT EXISTS vector; therefore runs as is. Any other extension (postgis…) is refused (0A000).

Others: cached views (34.7), enum(…) and set(…) columns (33.11), CREATE OR REPLACE TABLE. The other MIRAJ extensions are listed in chapter 30 of the general manual. Routines and triggers are written in PL/pgSQL: see 40. Routines and triggers.

34.13 Unsupported features and differences#

Missing statements#

Behavior differences#

Points to watch#

The following behaviors have been observed; they call for care in a script.

• DROP TABLE … CASCADE refused because of a foreign key: the XX000 error is returned and nothing is dropped, neither the table nor the views that read it.
• DROP INDEX of a name held by two tables on an equal footing (indexes created in the MIRAJ language, or UNIQUE columns of the same name without an index under a written name): refused (42710); write DROP INDEX name ON table.
• CREATE SCHEMA s followed by a CREATE FUNCTION: refused (0A000); only tables, views, indexes, sequences, triggers and GRANT are schema elements. The function of a trigger written as a schema element therefore comes from another schema: its name is written qualified (EXECUTE FUNCTION outils.f()).
• Named constraint already taken: CREATE TABLE t (…, CONSTRAINT name UNIQUE (…)) is refused (42P07) if name is already that of a table, a view, a sequence or an index under a written name of another table of the schema, like CREATE INDEX.
• ALTER SEQUENCE … MINVALUE | MAXVALUE that would leave out the sequence's current value: refused (22023, RESTART value (10) cannot be greater than MAXVALUE (7)); add RESTART [WITH n].
• DROP VIEW of the name of a table read by views: the refusal is returned with a dependency message (cannot drop view article because other objects depend on it); the table is not dropped.
• DROP SEQUENCE of a sequence cited by a DEFAULT nextval(…): refused (2BP01) without CASCADE, which removes those default values; likewise DROP FUNCTION of a function called by a default value.
• Function as a default value: executed read-only; a function that writes makes the row write fail (0A000).
• SQLSTATE XX000: a few MIRAJ-specific definition refusals (ON DELETE SET DEFAULT, non-deterministic expression in a CHECK, table still referenced) carry this generic code; rely on the message. The ordinary foreign key refusals (42P01, 42703, 42830, 42804) and the exhausted sequence (2200H) carry the PostgreSQL code.

See also#

• 33. Data types: types, default values, identity, generated columns.
• 35. DML language: INSERT, UPDATE, DELETE, ON CONFLICT, COPY.
• 38. Transactions and session: search_path, transactions.
• 39. Accounts and privileges: privileges on schemas and tables.
• 40. Routines and triggers: CREATE FUNCTION, CREATE TRIGGER.
• 41. Catalog and tools: pg_catalog, information_schema, psql.
• Chapter 30 and chapter 7.