Tutoriel PostgreSQL SQL

← Retour aux tutoriels

Pour compléter cette introduction, consultez aussi le tutoriel PostgreSQL avancé et administration et le tutoriel PostgreSQL PL/pgSQL.

Objectif du tutoriel

Cette page sert à deux choses : reprendre les bases utiles de PostgreSQL en SQL, puis donner à un profil expert Oracle les différences concrètes à connaître pour adapter rapidement ses requêtes sans perdre de temps sur les pièges les plus fréquents.

L'idée n'est pas de tout couvrir sur PostgreSQL, mais de fournir une base directement exploitable pour lire, écrire, migrer et corriger des requêtes du quotidien.

Parcours d'apprentissage

• Niveau 1 : SELECT, WHERE, ORDER BY, LIMIT, OFFSET, NULL, alias et jointures.
• Niveau 2 : agrégations, sous-requêtes, CTE, window functions, dates et textes.
• Niveau 3 : adaptation de requêtes Oracle vers PostgreSQL, avec correspondances utiles et différences de comportement.

Conseil : si vous venez d'Oracle, commencez par lire les exemples PostgreSQL, puis la section Oracle vers PostgreSQL en fin de page.

1. Requêtes de base

SELECT

-- Sélection simple
SELECT * FROM employees;

SELECT employee_id, first_name, last_name, salary
FROM employees;

-- DISTINCT
SELECT DISTINCT department_id
FROM employees;

-- Alias
SELECT
    first_name AS prenom,
    last_name AS nom,
    salary * 12 AS salaire_annuel
FROM employees;

-- Concaténation
SELECT first_name || ' ' || last_name AS nom_complet
FROM employees;

WHERE

-- Comparaison
SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE hire_date >= DATE '2024-01-01';

-- BETWEEN / IN
SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000
  AND department_id IN (10, 20, 30);

-- LIKE / ILIKE
SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE email ILIKE '%@example.com';

-- IS NULL
SELECT * FROM employees WHERE commission_pct IS NULL;

-- Regex
SELECT * FROM employees
WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';

2. Tri, pagination et limitation

-- ORDER BY
SELECT * FROM employees
ORDER BY salary DESC;

SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

-- NULLS FIRST / NULLS LAST
SELECT * FROM employees
ORDER BY commission_pct NULLS LAST;

-- LIMIT
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;

-- OFFSET
SELECT * FROM employees
ORDER BY employee_id
OFFSET 20 LIMIT 10;

-- SQL standard également supporté
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

3. Jointures et sous-requêtes

JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

SELECT e.first_name AS employe, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Sous-requêtes

-- Plus que la moyenne globale
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- EXISTS
SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
      AND d.location_id = 1700
);

-- Corrélation
SELECT e1.first_name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

4. Agrégations et fenêtres

-- Agrégations
SELECT
    department_id,
    COUNT(*) AS nb_employés,
    AVG(salary) AS salaire_moyen,
    SUM(salary) AS masse_salariale
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000
ORDER BY department_id;

-- Window functions
SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rang,
    AVG(salary) OVER (PARTITION BY department_id) AS moyenne_departement
FROM employees;

PostgreSQL est très fort sur les window functions. Si vous faites déjà du SQL analytique sous Oracle, vous retrouverez l'essentiel avec une syntaxe très proche.

5. Dates, chaînes et NULL

-- Maintenant
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();

-- Formatage
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');

-- Conversion texte vers date
SELECT TO_DATE('2026-05-31', 'YYYY-MM-DD');
SELECT TO_TIMESTAMP('2026-05-31 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- Null handling
SELECT COALESCE(commission_pct, 0) AS commission
FROM employees;

-- Chaînes
SELECT UPPER(last_name), LENGTH(last_name), SUBSTRING(last_name FROM 1 FOR 3)
FROM employees;

-- Date truncation
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);

Attention : PostgreSQL distingue fortement les types, et la conversion implicite est souvent moins permissive que dans des usages Oracle historiques.

6. INSERT, UPDATE, DELETE et upsert

-- INSERT
INSERT INTO departments (department_id, department_name)
VALUES (70, 'Data');

-- INSERT avec RETURNING
INSERT INTO departments (department_name)
VALUES ('Platform')
RETURNING department_id;

-- UPDATE
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10;

-- DELETE
DELETE FROM employees
WHERE employee_id = 999;

-- UPSERT PostgreSQL
INSERT INTO departments (department_id, department_name)
VALUES (10, 'Sales')
ON CONFLICT (department_id)
DO UPDATE SET department_name = EXCLUDED.department_name;

7. CTE et récursion

-- CTE simple
WITH salaires AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.first_name, e.salary, s.avg_salary
FROM employees e
JOIN salaires s ON s.department_id = e.department_id;

-- CTE récursive (équivalent fréquent de CONNECT BY)
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, manager_id, first_name, 1 AS niveau
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.first_name, h.niveau + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT *
FROM hierarchy
ORDER BY niveau, employee_id;

8. Oracle vers PostgreSQL : différences SQL à connaître

Cette section cible directement le cas d'un utilisateur Oracle qui veut adapter vite ses requêtes. Le plus efficace est de mémoriser les changements de réflexe suivants.

Correspondances directes

Oracle                          PostgreSQL
------------------------------  ------------------------------------------
SYSDATE                         CURRENT_DATE ou CURRENT_TIMESTAMP ou NOW()
SYSTIMESTAMP                    CURRENT_TIMESTAMP
NVL(a, b)                       COALESCE(a, b)
NVL2(a, b, c)                   CASE WHEN a IS NOT NULL THEN b ELSE c END
DECODE(x, 1, 'A', 2, 'B', 'X')  CASE x WHEN 1 THEN 'A' WHEN 2 THEN 'B' ELSE 'X' END
LISTAGG(col, ',')               STRING_AGG(col, ',')
MINUS                           EXCEPT
ROWNUM                          LIMIT ou ROW_NUMBER() OVER (...)
CONNECT BY                      WITH RECURSIVE
DUAL                            inutile dans la plupart des cas
VARCHAR2                        VARCHAR
NUMBER                          NUMERIC ou INTEGER ou BIGINT selon le cas

Exemples de traduction

-- Oracle
SELECT SYSDATE FROM dual;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP;

-- Oracle
SELECT NVL(commission_pct, 0)
FROM employees;

-- PostgreSQL
SELECT COALESCE(commission_pct, 0)
FROM employees;

-- Oracle
SELECT * FROM employees WHERE ROWNUM <= 10;

-- PostgreSQL
SELECT * FROM employees LIMIT 10;

-- Oracle
SELECT * FROM a
MINUS
SELECT * FROM b;

-- PostgreSQL
SELECT * FROM a
EXCEPT
SELECT * FROM b;

9. Traduction de requêtes Oracle réelles vers PostgreSQL

Voici une série de cas typiques que l'on rencontre très souvent quand on passe d'Oracle à PostgreSQL. Le but n'est pas seulement de mémoriser une équivalence, mais d'adopter le bon réflexe PostgreSQL.

1. Tester une expression simple

-- Oracle
SELECT SYSDATE, USER FROM dual;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP, CURRENT_USER;

2. Remplacer NVL

-- Oracle
SELECT employee_id, NVL(commission_pct, 0) AS commission
FROM employees;

-- PostgreSQL
SELECT employee_id, COALESCE(commission_pct, 0) AS commission
FROM employees;

3. Remplacer NVL2

-- Oracle
SELECT employee_id,
    NVL2(commission_pct, 'commissionnée', 'sans commission') AS statut
FROM employees;

-- PostgreSQL
SELECT employee_id,
       CASE
           WHEN commission_pct IS NOT NULL THEN 'commissionnée'
           ELSE 'sans commission'
       END AS statut
FROM employees;

4. Remplacer DECODE

-- Oracle
SELECT employee_id,
       DECODE(status, 'A', 'Actif', 'I', 'Inactif', 'Inconnu') AS libelle
FROM users_app;

-- PostgreSQL
SELECT employee_id,
       CASE status
           WHEN 'A' THEN 'Actif'
           WHEN 'I' THEN 'Inactif'
           ELSE 'Inconnu'
       END AS libelle
FROM users_app;

5. Limiter les lignes

-- Oracle
SELECT *
FROM employees
WHERE ROWNUM <= 5;

-- PostgreSQL
SELECT *
FROM employees
LIMIT 5;

6. Pagination

-- Oracle 12c+
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- PostgreSQL
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 20 LIMIT 10;

7. Hierarchie CONNECT BY

-- Oracle
SELECT employee_id, manager_id, first_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- PostgreSQL
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, manager_id, first_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.first_name, h.level + 1
    FROM employees e
    JOIN hierarchy h ON h.employee_id = e.manager_id
)
SELECT employee_id, manager_id, first_name, level
FROM hierarchy;

8. Différence entre deux jeux de résultats

-- Oracle
SELECT employee_id FROM anciens_employes
MINUS
SELECT employee_id FROM employees;

-- PostgreSQL
SELECT employee_id FROM anciens_employes
EXCEPT
SELECT employee_id FROM employees;

9. Agrégation de chaînes

-- Oracle
SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS noms
FROM employees
GROUP BY department_id;

-- PostgreSQL
SELECT department_id,
       STRING_AGG(last_name, ', ' ORDER BY last_name) AS noms
FROM employees
GROUP BY department_id;

10. Date du jour sans heure

-- Oracle
SELECT TRUNC(SYSDATE) FROM dual;

-- PostgreSQL
SELECT CURRENT_DATE;

-- Variante si on part d'un timestamp
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP);

11. Premier jour du mois

-- Oracle
SELECT TRUNC(SYSDATE, 'MM') FROM dual;

-- PostgreSQL
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);

12. Ajouter des mois

-- Oracle
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

-- PostgreSQL
SELECT CURRENT_DATE + INTERVAL '3 months';

13. Longueur d'une chaîne et sous-chaîne

-- Oracle
SELECT LENGTH(last_name), SUBSTR(last_name, 1, 3)
FROM employees;

-- PostgreSQL
SELECT LENGTH(last_name), SUBSTRING(last_name FROM 1 FOR 3)
FROM employees;

14. Recherche insensible à la casse

-- Oracle
SELECT *
FROM employees
WHERE UPPER(last_name) LIKE UPPER('sm%');

-- PostgreSQL
SELECT *
FROM employees
WHERE last_name ILIKE 'sm%';

15. Upsert metier

-- Oracle (souvent MERGE)
MERGE INTO departments d
USING (SELECT 10 AS department_id, 'Sales' AS department_name FROM dual) s
ON (d.department_id = s.department_id)
WHEN MATCHED THEN
    UPDATE SET d.department_name = s.department_name
WHEN NOT MATCHED THEN
    INSERT (department_id, department_name)
    VALUES (s.department_id, s.department_name);

-- PostgreSQL
INSERT INTO departments (department_id, department_name)
VALUES (10, 'Sales')
ON CONFLICT (department_id)
DO UPDATE SET department_name = EXCLUDED.department_name;

16. Récupérer une clé générée après insert

-- Oracle
INSERT INTO departments (department_id, department_name)
VALUES (seq_departments.NEXTVAL, 'Support')
RETURNING department_id INTO :v_department_id;

-- PostgreSQL
INSERT INTO departments (department_name)
VALUES ('Support')
RETURNING department_id;

17. Jointure externe Oracle (+)

-- Oracle ancien style
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

-- PostgreSQL
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

18. Sequence explicite

-- Oracle
SELECT seq_factures.NEXTVAL FROM dual;

-- PostgreSQL
SELECT nextval('seq_factures');

19. Top 1 par groupe

-- Oracle
SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees e
)
WHERE rn = 1;

-- PostgreSQL
SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees e
) sub
WHERE rn = 1;

20. Variante PostgreSQL très pratique avec DISTINCT ON

-- Équivalent fréquent pour garder la première ligne par groupe
SELECT DISTINCT ON (department_id)
       department_id, employee_id, first_name, salary
FROM employees
ORDER BY department_id, salary DESC;

21. Cast explicite

-- Oracle
SELECT TO_NUMBER('123'), TO_CHAR(456)
FROM dual;

-- PostgreSQL
SELECT CAST('123' AS NUMERIC), CAST(456 AS TEXT);

-- Variante PostgreSQL courte
SELECT '123'::NUMERIC, 456::TEXT;

22. Filtrage regex

-- Oracle
SELECT *
FROM clients
WHERE REGEXP_LIKE(email, '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$', 'i');

-- PostgreSQL
SELECT *
FROM clients
WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';

23. Null et chaîne vide

-- Oracle : '' est souvent traite comme NULL
SELECT NVL(code_interne, 'N/A')
FROM clients;

-- PostgreSQL : '' reste une vraie chaîne vide
SELECT COALESCE(NULLIF(code_interne, ''), 'N/A')
FROM clients;

24. Dual remplace par VALUES

-- Oracle
SELECT 10 AS id, 'test' AS libelle FROM dual;

-- PostgreSQL
SELECT 10 AS id, 'test' AS libelle;

-- Ou quand on veut un petit jeu de données inline
VALUES (10, 'test'), (20, 'demo');

10. Pièges fréquents quand on vient d'Oracle

• Pas de table DUAL : en PostgreSQL, SELECT NOW(); suffit.
• Chaîne vide : Oracle traite souvent '' comme NULL, PostgreSQL non.
• Jointures Oracle (+) : non supportées, utilisez uniquement les jointures ANSI.
• Pagination : le réflexe habituel est LIMIT et OFFSET, même si FETCH FIRST existe.
• MERGE : PostgreSQL moderne le supporte, mais le réflexe courant reste souvent INSERT ... ON CONFLICT.
• Date TRUNC : en Oracle on pense souvent a TRUNC(date), en PostgreSQL on utilise souvent DATE_TRUNC().
• Sensibilité à la casse : les identifiants non quotes sont normalisés en minuscules. Évitez les noms quotes inutilement.
• Fonctions custom et conversions : PostgreSQL est souvent plus strict sur les casts ; ajoutez-les explicitement si besoin.

11. Mini pense-bête Oracle vers PostgreSQL

• Pour tester une valeur : remplacez SELECT ... FROM dual par SELECT ....
• Pour les NULL : remplacez NVL par COALESCE et DECODE par CASE.
• Pour les hierarchies : remplacez CONNECT BY par WITH RECURSIVE.
• Pour les écarts entre jeux : remplacez MINUS par EXCEPT.
• Pour les totaux de chaînes : pensez à STRING_AGG à la place de LISTAGG.
• Pour un identifiant auto-généré : regardez GENERATED ... AS IDENTITY ou les séquences avec nextval().

Liens utiles dans le site

Pour les rappels Oracle, consultez aussi le tutoriel Oracle SQL et le tutoriel Oracle PL/SQL. Pour prolonger PostgreSQL, consultez aussi PostgreSQL avancé et administration et PostgreSQL PL/pgSQL. Pour les usages SQL orientés données et IA, voir également la page IA Oracle / SQL / PL/SQL.