31. PostgreSQL: Getting started

This chapter guides you step by step: starting a MIRAJ instance in the PostgreSQL language, connecting to it with a PostgreSQL client (psql, psycopg2, a driver) or with the miraj-cli console, creating a schema and tables, writing and reading data, indexing and inspecting the schema. At the end, you will be able to run a complete use case without leaving PostgreSQL syntax.

Results are shown as a PostgreSQL client receives them: a table of rows, then the statement tag (SELECT 3, INSERT 0 1, CREATE TABLE...). Errors are given with their SQLSTATE.

31.1 Starting the server in the PostgreSQL language#

A MIRAJ instance speaks a single SQL language, chosen at startup by the POSTGRESQL element of sql_mode:

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

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

The langage PostgreSQL mention confirms the choice; the end of the line shows the installed license. The D:\donnees folder is created if it does not exist, with a local root account whose randomly generated password is displayed only once, just above: note it down, then replace it (31.9). Leave this window open: the server keeps running in it.

Item	Value
Port	the server's main port (`--port`, 7007 by default): there is no second port, and port 5432 is not used
Language	fixed for the whole instance; changing it requires a restart, and the databases and their data remain valid
Permanent setting	`<sql_mode>STRICT_TRANS_TABLES,POSTGRESQL</sql_mode>` in `miraj_config.xml`, or the SQL language choice in the configuration editor
Check	`SHOW sql_mode;` returns `STRICT_TRANS_TABLES,POSTGRESQL`

The server's other options (data folder, journal, TLS, Windows service) do not depend on the language: see server administration, and its section 15.10 for what is specific to an instance in the PostgreSQL language.

31.2 What to know before connecting#

A PostgreSQL client sees the instance as follows:

PostgreSQL concept	In MIRAJ
database (`dbname`)	only one: `miraj`
schema	a MIRAJ database; `CREATE SCHEMA ventes` and `CREATE DATABASE ventes` create the same thing
current schema, `search_path`	the session's current database; `SET search_path TO ventes` selects it
`public`	is not a schema: in a name (`public.client`), it is an alias for the current database
announced version	`PostgreSQL 16.4 (MIRAJ 31.0.0)` (`server_version` = `16.4`)
encoding	UTF-8 only (`client_encoding` = `UTF8`)

The dbname connection parameter therefore does not select a PostgreSQL database, but the current schema:

`dbname` given	Effect
name of a MIRAJ database (`ventes`)	it becomes the current schema
`miraj`, `postgres`, the account name, or nothing	ignored: the connection opens without a current schema (`current_schema` is NULL)
any other, unknown name	notice `NOTICE: database "inconnue" does not exist; connected without a current database`, and the connection opens without a current schema

Without a current schema, tables are named schema.table, or you choose one with SET search_path TO ventes;.

31.3 Connecting with psql#

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

On Windows, switch the console to UTF-8 (chcp 65001) or set PGCLIENTENCODING=UTF8: accented text typed in another code page is rejected. The description commands (\dn, \dt, \d table...) work; they are described in chapter 40. Catalog and tools.

First check, valid in any client:

SELECT version();

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

SELECT current_database(), current_schema, current_user;

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

31.4 Connecting with miraj-cli#

The SQL console shipped with MIRAJ also connects to an instance in the PostgreSQL language: it recognizes the language at connection time. The password is given with --password (hidden input) or through the MIRAJ_PASSWORD environment variable.

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

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

The ventes=> prompt shows the current database (miraj=> without a current database); each statement ends with a semicolon and is written in the PostgreSQL language. Results are framed in the miraj-cli style:

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

A script file is run by naming it (miraj-cli --port 7007 script.sql), one statement per -e "...". Details in chapter 40.

31.5 Connecting from an application#

Any PostgreSQL driver works; only the host, the port and the schema name differ from a PostgreSQL server.

Python (psycopg2)#

The example reads and writes the client table created in 31.7.

import psycopg2

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

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

The URI form is accepted too: psycopg2.connect("postgresql://root:[email protected]:7007/ventes").

Values come back in the usual Python types: int, str, bool for a boolean, decimal.Decimal for a numeric, datetime.date, datetime.datetime (timezone-aware for a timestamptz, like now()), dict for a jsonb.
psycopg2 opens a transaction at the first statement: nothing is kept without conn.commit() (or conn.autocommit = True).
The identifier of an inserted row is read with INSERT ... RETURNING id; cursor.lastrowid is always 0.
After an error in a transaction, the following statements receive 25P02 until conn.rollback(), as on a PostgreSQL server.

Other drivers#

Driver	Connection string
Npgsql (.NET)	`Host=127.0.0.1;Port=7007;Username=root;Password=…;Database=ventes`
pgjdbc (Java)	`jdbc:postgresql://127.0.0.1:7007/ventes`
ODBC (MIRAJ ODBC Driver)	`DRIVER={MIRAJ ODBC Driver};SERVER=127.0.0.1;PORT=7007;UID=root;PWD=…;DATABASE=ventes`

Chapter 40 describes each driver, what it supports and its limits.

Connection refusals#

Case	What the client receives
wrong password or unknown account	`FATAL: Access denied for user 'root'@'localhost' (using password: YES)` (SQLSTATE `28P01`); nothing distinguishes the two cases
`sslmode=require` on a server started without a certificate	`server does not support SSL, but SSL was required` (client message)
legacy protocol client (MIRAJ language)	the connection stays silent and is then closed by the server: this client does not talk to a PostgreSQL instance

Authentication (SCRAM-SHA-256) and TLS encryption are described in chapter 38. Accounts and privileges.

31.6 Creating a schema#

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE SCHEMA
SET

SET search_path makes ventes the session's current schema; SHOW search_path; and SELECT current_schema; then return ventes. In an application, give the schema at connection time instead (dbname=ventes).

31.7 Creating tables#

Four tables: customers, articles, orders and their lines. serial gives an auto-incremented key; the NOT NULL, UNIQUE, CHECK, REFERENCES and DEFAULT constraints are written as in PostgreSQL.

CREATE TABLE client (
    id       serial PRIMARY KEY,
    nom      varchar(80) NOT NULL,
    email    varchar(120) NOT NULL UNIQUE,
    ville    text,
    actif    boolean NOT NULL DEFAULT true,
    cree_le  timestamp DEFAULT now()
);

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

CREATE TABLE commande (
    id          serial PRIMARY KEY,
    client_id   integer NOT NULL REFERENCES client (id),
    passee_le   date NOT NULL DEFAULT current_date,
    etat        varchar(12) NOT NULL DEFAULT 'ouverte'
);

CREATE TABLE ligne_commande (
    commande_id integer NOT NULL REFERENCES commande (id) ON DELETE CASCADE,
    article_id  integer NOT NULL REFERENCES article (id),
    quantite    integer NOT NULL CHECK (quantite > 0),
    prix_unit   numeric(10,2) NOT NULL,
    PRIMARY KEY (commande_id, article_id)
);

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

Types and constraints are detailed in chapters 32. Data types and 33. Data definition language (DDL).

31.8 Writing data#

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

INSERT 0 3

RETURNING returns the rows written, with their generated values:

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

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

INSERT 0 3

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

INSERT 0 3
INSERT 0 4

Constraints are enforced, with PostgreSQL's SQLSTATE codes:

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

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

The SQLSTATE is PostgreSQL's; the message text is MIRAJ's, in English, and quotes names in its own way. An application must therefore rely on the SQLSTATE, not on the text.

Modify a row, then insert or update depending on the case (ON CONFLICT):

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

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

UPDATE 1

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

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

INSERT 0 1

A transaction is opened with BEGIN and ended with COMMIT or ROLLBACK. Here, a deletion and an update are cancelled:

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

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

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

See 34. DML language and 37. Transactions and session.

31.9 Changing the root password and creating an account#

The temporary root password is replaced like this:

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

An application should not connect as root. An account and its rights on the schema:

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

CREATE USER
GRANT

The app account then reads and writes the tables of ventes, and receives 42501 for anything else (DROP TABLE client: permission denied for table client).

The account can also be created in PostgreSQL's own forms, which return their own tag:

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

CREATE USER
CREATE ROLE

Rights on a whole schema are granted with GRANT ... ON schema.*, as above. See 38. Accounts and privileges.

31.10 Querying the data#

Filtering and sorting; ILIKE ignores case, LIKE respects it, as in PostgreSQL:

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

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

:: conversion and booleans (shown as t / f):

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

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

Join and aggregate, amount of each order:

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

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

Total per customer, including customers with no orders; NULLS LAST places the missing total at the end of the list:

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

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

See 35. SELECT queries and 36. SQL functions.

31.11 Creating an index#

An index speeds up searches on a frequently filtered column that is neither a primary key nor UNIQUE:

CREATE INDEX idx_commande_date ON commande (passee_le);

CREATE INDEX

It is dropped without naming the table, as in PostgreSQL: DROP INDEX idx_commande_date; (tag DROP INDEX).

An index placed on the column of a foreign key (CREATE INDEX ... ON commande (client_id)) is dropped like any other: the constraint is still checked, more slowly without it. An index name is unique within its schema (42P07 for a second index with the same name, even on another table).

31.12 Viewing the schema#

With psql, \dt lists the tables and \d client describes a table. Without psql, the same information can be read in pg_catalog and information_schema, or through MIRAJ's SHOW statements, which remain available.

List the tables of the current schema:

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

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

SHOW TABLES; returns the same list (column Tables_in_ventes), and SHOW DATABASES; the schemas.

Describe the columns of a table:

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

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

View the complete definition of a table, written in the PostgreSQL language:

SHOW CREATE TABLE commande;

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

List the indexes:

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

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

Chapter 40. Catalog and tools describes pg_catalog and information_schema.

31.13 Differences to know from the start#

MIRAJ speaks PostgreSQL's grammar, but it is its own engine that executes. The differences encountered earliest:

Topic	On a MIRAJ instance in the PostgreSQL language
Database and schema	a single `miraj` database; one schema per MIRAJ database; no `public` schema (31.2)
Name case	all identifiers are case-insensitive, even in double quotes: `"Client"` and `client` designate the same table
Text case	text comparisons are case-sensitive (`nom = 'AMEL KACI'` does not find `Amel Kaci`); `LIKE` and `~` respect case, `ILIKE` and `~*` ignore it, as in PostgreSQL
Division	between two integers, `/` is an integer division: `7 / 2` is `3`, like `div(7, 2)`; `7.0 / 2` is `3.5000000000000000`
Division by zero	error `22012`, like PostgreSQL
Constraint names	MIRAJ's own: primary key `<table>_pkey`, uniqueness named after the column (`email`), foreign keys `<table>_ibfk_<n>`, `CHECK` `CONSTRAINT_<n>`
Error messages	PostgreSQL SQLSTATE, MIRAJ text (31.8)
Accounts and rights	`CREATE USER ... IDENTIFIED BY` or `WITH PASSWORD`, rights on a schema with `GRANT ... ON schema.*` (31.9)
Isolation level	`SHOW transaction_isolation` returns `read committed` by default
Startup parameters	the connection `options` (`-c statement_timeout=5000`, `--lock_timeout=2s`) are applied as so many `SET` statements; an unknown option (`42704`) or one reserved for PostgreSQL server startup (`shared_buffers`...: `55P02`) refuses the connection; the parameters of the drivers' startup message (`TimeZone`, `DateStyle`, `extra_float_digits`...) are applied too, an unknown name being ignored there

Syntax specific to the MIRAJ language is rejected on such an instance, with a 42601 error that names the construct:

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

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

The complete list of what is accepted, rejected or not yet supported is in chapter 29.

31.14 What next#

You now know how to start an instance in the PostgreSQL language, connect to it, create a schema and tables, write, read, index and inspect the schema. The following chapters detail each topic:

32. Data types to choose a column's type,
33. Data definition language (DDL) and 34. DML language for definition and write statements,
35. SELECT queries and 36. SQL functions for reading,
37. Transactions and session for transactions and session settings,
38. Accounts and privileges for accounts, authentication and TLS,
39. Routines and triggers (PL/pgSQL) for PL/pgSQL,
40. Catalog and tools for pg_catalog, clients, drivers and MIRAJ's tools.