RequĂȘtes SQL

29 août 2024

Dalibo SCOP

Sur ce document

Formation Module N3
Titre RequĂȘtes SQL
RĂ©vision 24.09
PDF https://dali.bo/n3_pdf
EPUB https://dali.bo/n3_epub
HTML https://dali.bo/n3_html
Slides https://dali.bo/n3_slides
TP https://dali.bo/n3_tp
TP (solutions) https://dali.bo/n3_solutions

Vous trouverez en ligne les différentes versions complÚtes de ce document.


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses annĂ©es d’études, d’expĂ©rience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communautĂ© du logiciel.

Au‑delĂ  du contenu technique en lui‑mĂȘme, notre intention est de transmettre les valeurs qui animent et unissent les dĂ©veloppeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, crĂ©ativitĂ©, dynamisme
 Le but premier de nos formations est de vous aider Ă  mieux exploiter toute la puissance de PostgreSQL mais nous espĂ©rons Ă©galement qu’elles vous inciteront Ă  devenir un membre actif de la communautĂ© en partageant Ă  votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur Ă  maintenir nos manuels Ă  jour, avec des informations prĂ©cises et des exemples dĂ©taillĂ©s. Toutefois malgrĂ© nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprĂ©cisions ou des erreurs. Si vous constatez un souci, n’hĂ©sitez pas Ă  le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit LobrĂ©au, Jean‑Louis LouĂ«r, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, MaĂ«l Rimbault, Julien Rouhaud, StĂ©phane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, CĂ©dric Villemain, Thibaud Walkowiak, FrĂ©dĂ©ric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurĂ©es autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux dĂ©tails qui ne peuvent ĂȘtre donnĂ©es Ă  l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous ĂȘtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • PaternitĂ©
  • Pas d’utilisation commerciale
  • Partage des conditions initiales Ă  l’identique

Vous n’avez pas le droit d’utiliser cette crĂ©ation Ă  des fins commerciales.

Si vous modifiez, transformez ou adaptez cette crĂ©ation, vous n’avez le droit de distribuer la crĂ©ation qui en rĂ©sulte que sous un contrat identique Ă  celui-ci.

Vous devez citer le nom de l’auteur original de la maniĂšre indiquĂ©e par l’auteur de l’Ɠuvre ou le titulaire des droits qui vous confĂšre cette autorisation (mais pas d’une maniĂšre qui suggĂ©rerait qu’ils vous soutiennent ou approuvent votre utilisation de l’Ɠuvre). À chaque rĂ©utilisation ou distribution de cette crĂ©ation, vous devez faire apparaĂźtre clairement au public les conditions contractuelles de sa mise Ă  disposition. La meilleure maniĂšre de les indiquer est un lien vers cette page web. Chacune de ces conditions peut ĂȘtre levĂ©e si vous obtenez l’autorisation du titulaire des droits sur cette Ɠuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mĂȘmes et les travaux pratiques. Cette formation peut Ă©galement contenir quelques images et schĂ©mas dont la redistribution est soumise Ă  des licences diffĂ©rentes qui sont alors prĂ©cisĂ©es.

Marques déposées

PostgreSQLŸ PostgresŸ et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Sur les versions prĂ©cĂ©dentes susceptibles d’ĂȘtre encore rencontrĂ©es en production, seuls quelques points trĂšs importants sont Ă©voquĂ©s, en plus Ă©ventuellement de quelques Ă©lĂ©ments historiques.

Sauf prĂ©cision contraire, le systĂšme d’exploitation utilisĂ© est Linux.

RequĂȘtes SQL


Introduction

Ce module est organisé en quatre parties :

  • CompatibilitĂ© avec Oracle
  • Types de donnĂ©es
  • DiffĂ©rences de syntaxes
  • Transactions

AprĂšs avoir migrĂ© les donnĂ©es, il faut Ă©galement retravailler Ă  minima les requĂȘtes de façon Ă  ce qu’elles puissent s’exĂ©cuter sur PostgreSQL. Le langage SQL Ă©tant issu d’une norme ISO qui Ă©volue constamment, le travail n’est pas aussi important que s’il s’agissait d’une rĂ©Ă©criture dans un nouveau langage.

Mais certaines formes d’écritures peuvent poser problĂšme. Elles sont hĂ©ritĂ©es des temps oĂč Oracle offrait ses propres extensions au langage SQL avant que les fonctionnalitĂ©s ne soient disponibles dans la norme SQL. Bien qu’Oracle supporte maintenant les derniĂšres avancĂ©es de la norme SQL, de nombreuses applications Ă  migrer utilisent encore le dialecte SQL. Ce chapitre a pour objectif de prĂ©senter les principaux Ă©lĂ©ments qui nĂ©cessitent une rĂ©Ă©criture.


Compatibilité avec Oracle

Oracle et PostgreSQL sont assez proches :

  • Tous deux des SGBDR
  • Le langage d’accĂšs aux donnĂ©es est SQL
  • Les deux ont des connecteurs pour la majoritĂ© des langages (Java, C, .Net
)
  • Les langages embarquĂ©s sont diffĂ©rents
  • C’est dans les dĂ©tails que se trouvent les problĂšmes

Les SGBD Oracle et PostgreSQL partagent beaucoup de fonctionnalitĂ©s. MĂȘme si l’implĂ©mentation est diffĂ©rente, les fonctionnalitĂ©s se ressemblent beaucoup.

Tous les deux sont des systÚmes de gestion de bases de données relationnelles. Tous les deux utilisent le langage SQL (leur support de la norme diffÚre évidemment).

Tous les deux ont des connecteurs pour la majoritĂ© des langages actuels (l’efficacitĂ© et le support des fonctionnalitĂ©s du moteur dĂ©pendent de l’implĂ©mentation des connecteurs). Par contre, les langages autorisĂ©s pour les routines stockĂ©es sont diffĂ©rents, y compris ceux qui sont disponibles par dĂ©faut.

MĂȘme si les fonctionnalitĂ©s majeures sont prĂ©sentes dans les deux moteurs, les dĂ©tails d’implĂ©mentation et de mise en place sont le cƓur du problĂšme. Cette partie dresse une liste non exhaustive des diffĂ©rences majeures entre Oracle et PosgreSQL pour mieux apprĂ©hender les problĂšmes ou des incomprĂ©hensions lors d’une migration.


Points communs

PostgreSQL et Oracle :

  • Ont le mĂȘme langage d’accĂšs aux donnĂ©es (SQL)
    • mais des « variantes » diffĂ©rentes (extensions au standard)
  • Nombreux concepts en commun:
    • transactions et savepoints
    • MVCC et verrouillage
  • Conservation
    • des logiques applicative et algorithmique
    • de l’architecture applicative

PostgreSQL et Oracle partagent le mĂȘme langage d’accĂšs et de dĂ©finition des donnĂ©es. La norme SQL est plutĂŽt bien suivie par ces deux SGBD. NĂ©anmoins, tous les moteurs se permettent des Ă©carts par rapport Ă  la norme, parfois pour gagner en performances, mais surtout pour faciliter la vie des dĂ©veloppeurs. Puis ces Ă©carts persistent par la nĂ©cessaire compatibilitĂ© descendante.

Beaucoup de dĂ©veloppeurs utilisent donc ces Ă©carts Ă  la norme, souvent sans le savoir. Lors d’une migration, cela pose beaucoup de problĂšmes si de tels Ă©carts sont utilisĂ©s, car les autres moteurs de bases de donnĂ©es ne les implĂ©mentent pas tous (si tant est qu’ils en aient le droit). PostgreSQL essaie, quand cela est possible, de supporter les extensions de la norme rĂ©alisĂ©es par les autres moteurs. Les dĂ©veloppeurs de PostgreSQL s’assurent que si une telle extension est ajoutĂ©e, la version proposĂ©e par la norme soit elle aussi possible.

PostgreSQL et Oracle partagent aussi certains concepts, comme les transactions et les savepoints, MVCC (mĂȘme si l’implĂ©mentation diffĂšre) et la gestion des verrous. Cela permet de conserver les logiques applicative et algorithmique, au moins jusqu’à une certaine mesure.


Différences de schéma - 1

  • Le schĂ©ma sous Oracle : USER.OBJECT
    • sous PostgreSQL, vĂ©ritable espace de nommage
  • La crĂ©ation des tables est entiĂšrement compatible mais :
    • les tables temporaires globales n’existent pas sous PostgreSQL
    • INITTRANS, MAXEXTENTS sont inutiles (et n’existent pas)
    • PCTFREE correspond au paramĂštre fillfactor
    • PCTUSED est inutile (et n’existe pas)
  • Les colonnes gĂ©nĂ©rĂ©es sont disponibles depuis PostgreSQL 12
    • uniquement pour les colonnes stockĂ©es
    • utilisation de vues pour simuler les colonnes virtuelles

Les schémas

Sous PostgreSQL, les schĂ©mas sont de vĂ©ritables espaces de nommage dont on peut changer le propriĂ©taire, alors qu’un schĂ©ma Oracle n’est ni plus ni moins qu’un utilisateur auquel des objets seront associĂ©s.

Création de table

La dĂ©finition des tables est quasiment identique pour les deux SGBD Ă  la diffĂ©rence prĂšs que PostgreSQL ne supporte pas les tables temporaires globales, dont les donnĂ©es insĂ©rĂ©es ne persistent que le temps d’une transaction ou d’une session. Sous PostgreSQL, c’est la table elle-mĂȘme qui est supprimĂ©e Ă  la fin de la session.

L’extension pgtt de Gilles Darold permet d’émuler le comportement de tables temporaires globales : https://github.com/darold/pgtt

Il n’y a pas non plus de notion de rĂ©servation de nombre de transactions allouĂ©es Ă  chaque bloc ou d’extents.

PCTFREE qui indique (en pourcentage) l’espace que l’on souhaite conserver dans le bloc pour les mises Ă  jour, correspond au fillfactor sous PostgreSQL. PCTUSED n’existe pas (il n’a pas de sens dans l’implĂ©mentation de PostgreSQL).

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

Colonnes virtuelles

Pour remplacer les colonnes virtuelles, les vues sont idéales. Voici un exemple de définition de colonne virtuelle sous Oracle :

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  salary      NUMBER(9,2),
  commission  NUMBER(3),
  salary2     NUMBER GENERATED ALWAYS AS
              (ROUND(salary*(1+commission/100),2)) VIRTUAL
);

Et voici la version à base d’une vue dans PostgreSQL :

CREATE TABLE employees (
  id          bigint,
  first_name  varchar(10),
  salary      double precision,
  commission  integer
);

CREATE VIEW virt_employees AS SELECT id, first_name, salary, commission,
    (ROUND((salary*(1+commission/100))::numeric,2)) salary2
FROM employees;

Depuis PostgreSQL 12, il est possible de crĂ©er une colonne gĂ©nĂ©rĂ©e, dite stockĂ©e, pour permettre l’utilisation d’un index sur cette colonne. La colonne n’est pas vraiment « virtuelle ».

Voici un exemple de la syntaxe dans PostgreSQL :

CREATE TABLE employees (
  id          bigint,
  first_name  varchar(10),
  salary      double precision,
  commission  integer,
  salary2     double precision generated always as
              (ROUND((salary*(1+commission/100))::numeric,2)) STORED
);

En savoir plus : Colonnes générées


Différences de schéma - 2

  • Casse par dĂ©faut du nom des objets diffĂ©rente entre Oracle et PostgreSQL
  • Si casse non spĂ©cifiĂ©e :
    • majuscule sous Oracle
    • minuscule sous PostgreSQL
  • Forcer la casse
    • " " autour des identifiants

La casse par dĂ©faut des objets est diffĂ©rente entre Oracle et PostgreSQL. C’est d’ailleurs un rare exemple oĂč PostgreSQL s’écarte du standard SQL.

Lorsque les noms des objets ne sont pas Ă©crits entre guillemets doubles, Oracle les transforme en majuscule alors que PostgreSQL les transforme toujours en minuscule. S’ils sont Ă©crits entre guillemets doubles, les deux ont le mĂȘme comportement : le nom est pris tel qu’écrit.

Si vous avez crĂ©Ă© vos objets avec des guillemets doubles sous Oracle et que vous les exportez aussi avec des guillemets doubles, vous devrez toujours inclure ces guillemets doubles dans le code de vos requĂȘtes lorsque vous ferez appel Ă  un objet. C’est donc dĂ©conseillĂ©, sous Oracle comme sous PostgreSQL.

Exemple :

dev2=# CREATE TABLE toto(id integer);
CREATE TABLE
dev2=# CREATE TABLE TitI(id integer);
CREATE TABLE
dev2=# \d
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+-----------
 public | t1   | table | guillaume
 public | t3   | table | guillaume
 public | titi | table | guillaume
 public | toto | table | guillaume
(4 rows)

dev2=# CREATE TABLE "TitI"(id integer);
CREATE TABLE
dev2=# \d
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+-----------
 public | TitI | table | guillaume
 public | t1   | table | guillaume
 public | t3   | table | guillaume
 public | titi | table | guillaume
 public | toto | table | guillaume
(5 rows)

Différences de schéma - 3

  • Les contraintes sont identiques (clĂ©s primaires, Ă©trangĂšres et uniques, 
)
  • Les index : btree uniquement, les autres n’existent pas (bitmap principalement)
  • Les tablespaces : la mĂȘme chose dans sa fonctionnalitĂ© principale
  • Les types utilisateurs (CREATE TYPE) nĂ©cessitent une rĂ©Ă©criture
  • Les liens inter-bases (DBLINK) n’existent pas sauf sous forme d’extensions (dblink ou fdw)

Les contraintes

L’ensemble des contraintes fonctionne exactement de la mĂȘme maniĂšre, que ce soit pour les clĂ©s primaires, les clĂ©s Ă©trangĂšres et les clĂ©s uniques ou pour les contraintes CHECK et NOT NULL.

Les index

Pour les index, seule la forme BTREE correspond, les autres ne sont pas implĂ©mentĂ©es mais PostgreSQL dispose lui aussi d’autres types d’index. Quoi qu’il en soit, la plupart des index utilisĂ©s sont des index de type BTREE.

Les index BITMAP sur disque n’existent pas sous PostgreSQL. Ils sont crĂ©Ă©s en mĂ©moire si nĂ©cessaire Ă  partir des index de type BTREE.

Les index IOT ne sont pas non plus supportĂ©s et peuvent ĂȘtre simulĂ©s Ă  l’aide de la commande CLUSTER qui trie une table en fonction de l’index.

Tablespaces

Les tablespaces correspondent, dans leur fonctionnalitĂ© principale, Ă  ce qui est fait sur Oracle, Ă  savoir Ă  dĂ©finir un espace du systĂšme de fichiers oĂč un plusieurs objets de la base pourront ĂȘtre stockĂ©s. Il n’y a pas de notion de taille initiale ni d’extension du tablespace sous PostgreSQL si ce n’est les limites imposĂ©es par le systĂšme de fichiers.

Types utilisateur

L’ensemble des types pouvant ĂȘtre dĂ©fini par un utilisateur sont supportĂ©s avec plus ou moins d’adaptation. Il peut notamment ĂȘtre nĂ©cessaire de redĂ©finir des fonctions d’entrĂ©e/sortie dĂ©finissant le comportement lors d’une insertion/lecture sur les donnĂ©es du type. Dans la plupart des cas, il s’agit de types composites ou de tableaux parfaitement supportĂ©s par PostgreSQL.

Exemple de type composite version Oracle :

CREATE OR REPLACE TYPE phone_t AS OBJECT (
    a_code   CHAR(3),
    p_number CHAR(8)
);

et la version PostgreSQL :

CREATE TYPE phone_t AS (
    a_code   char(3),
    p_number char(8)
);

Exemple d’un tableau de type :

CREATE OR REPLACE TYPE phonelist AS VARRAY(50) OF phone_t;

qui sera traduit en :

CREATE TYPE phonelist AS (phonelist phone_t[50]);

dblink

PostgreSQL ne permet pas d’accĂ©der nativement Ă  une autre base de donnĂ©es Ă  l’intĂ©rieur d’une requĂȘte SQL. Il est cependant possible d’utiliser les extensions dblink ou Foreign Data Wrapper pour accĂ©der Ă  des donnĂ©es Ă  distance mais sans pour autant pouvoir utiliser une notation Ă  base de @ dans la requĂȘte.


Autres différences anecdotiques

  • HAVING et GROUP BY
    • Oracle permet GROUP BY aprĂšs HAVING
    • PostgreSQL impose GROUP BY avant HAVING
  • Table DUAL n’est pas nĂ©cessaire
  • Conversions implicites de et vers un type text
    • supportĂ© par Oracle
    • plus supportĂ© par PostgreSQL depuis la version 8.3
    • convertir explicitement :
         SELECT 1 = 'a'::text;

Bien que la documentation Oracle indique que la clause GROUP BY prĂ©cĂšde la clause HAVING, la grammaire Oracle autorise l’inverse. Il faut donc corriger les requĂȘtes Ă©crites de la façon HAVING 
 GROUP BY.

Les requĂȘtes de la forme suivante :

SELECT * FROM test HAVING count(*) > 3 GROUP BY i;

seront transposĂ©es de la façon suivante pour pouvoir s’exĂ©cuter sous PostgreSQL :

SELECT * FROM test GROUP BY i HAVING count(*) > 3;

De nombreuses requĂȘtes SQL avec Oracle utilisent la pseudo-table DUAL pour manipuler des valeurs issues de fonctions ou de variables, sans besoin de les extraire d’une table particuliĂšre. Or avec Oracle, les clauses SELECT et FROM sont insĂ©parables.

Avec PostgreSQL, les syntaxes suivantes sont correctes :

SELECT fonction();
SELECT current_timestamp;

Les conversions implicites de et vers un champ de type texte ont été supprimées sous PostgreSQL depuis la version 8.3.

Par exemple, il n’est pas possible de faire ce type de requĂȘte :

CREATE TABLE depts ( numero CHAR(2), nom VARCHAR(25) );

SELECT * FROM depts WHERE numero BETWEEN 0 AND 42;
-- ERROR:  operator does not exist: character >= integer
-- LIGNE 1 : SELECT * FROM depts WHERE numero BETWEEN 0 AND 42;

Si l’on veut pouvoir faire faire fonctionner cette requĂȘte, il faut prĂ©ciser explicitement la conversion Ă  rĂ©aliser :

SELECT * FROM depts WHERE numero::int BETWEEN 0 AND 42;

Avec Oracle, ce type de conversion est implicite.


Types de données

  • Plusieurs incompatibilitĂ©s
    • Oracle ne supporte pas bien la norme SQL
    • types numĂ©riques, chaĂźnes, binaires, dates
  • PostgreSQL fournit Ă©galement des types spĂ©cialisĂ©s

Différences sur les types numériques

  • Oracle ne gĂšre pas les types numĂ©riques « natifs » SQL :
    • smallint, integer, bigint
  • Le type numeric du standard SQL est appelĂ© number sous Oracle

Les types smallint, integer, bigint, float, real, double precision sont plus rapides que le type numeric sous PostgreSQL : ils utilisent directement les fonctions cùblées des processeurs. Il faut donc les privilégier.


Différences sur les types chaßnes

  • Pas de varchar2 dans PostgreSQL
    • mais varchar
    • varchar (n) : taille en nombre de caractĂšres
  • Le type text Ă©quivalent Ă  varchar sans taille (1 Go maximum)
  • Attention, sous Oracle, '' Ă©quivaut Ă  NULL
    • sous PostgreSQL, '' et NULL sont distincts
  • Un seul encodage par base
  • Collationnement par colonne

Au niveau de PostgreSQL, il existe trois types de données pour les chaßnes de caractÚres :

  • char (alias de character) ;
  • varchar (alias de character varying) ;
  • text.

Le type varchar2 d’Oracle est l’équivalent du type varchar de PostgreSQL. Il est possible de ne pas donner de taille Ă  une colonne de type varchar, ce qui revient Ă  la dĂ©clarer de type text. Dans ce cas, la taille maximale thĂ©orique est de 1 Go. Suivant l’encodage, le nombre de caractĂšres intĂ©grables dans la colonne diffĂšre.

En pratique, il n’y a pas de diffĂ©rence Ă  l’utilisation, en vitesse ou en taille, entre ces diffĂ©rents types de chaĂźnes. Noter que la taille de chaĂźne indiquĂ©e (par exemple dans varchar(5)) est bien exprimĂ©e en caractĂšres (mĂȘme s’il faut plusieurs octets pour stocker chacun).

Une grosse diffĂ©rence entre Oracle et PostgreSQL pour les chaĂźnes de caractĂšres tient dans la façon dont les chaĂźnes vides sont gĂ©rĂ©es : Oracle ne fait pas de diffĂ©rence entre une chaĂźne vide et une chaĂźne NULL. PostgreSQL fait cette diffĂ©rence. Du coup, tous les tests de chaĂźnes vides effectuĂ©es avec un IS NULL et tous les tests de chaĂźnes NULL effectuĂ©s avec une comparaison avec une chaĂźne vide ne donneront pas le mĂȘme rĂ©sultat avec PostgreSQL. Ces tests doivent ĂȘtre vĂ©rifiĂ©s systĂ©matiquement par les dĂ©veloppeurs d’applications et de routines stockĂ©es.

dev2=# SELECT cast('' AS varchar) IS NULL;
 ?column?
----------
 f
(1 row)

Au niveau encodage, PostgreSQL n’accepte qu’un encodage par base de donnĂ©es. L’encodage par dĂ©faut est UTF-8. Le collationnement se gĂšre ensuite colonne par colonne et peut ĂȘtre modifiĂ© au sein d’une requĂȘte (au niveau d’un ORDER BY ou d’un CREATE INDEX).


Différences sur le type booléen

  • Oracle n’a pas de type boolean
    • Ă©mulĂ© de diverses maniĂšres
  • Attention aux ORM (Hibernate) suite Ă  la migration de donnĂ©es
    • ils chercheront un boolean sous PostgreSQL alors que vous aurez migrĂ© un int

Comme Oracle ne dispose pas d’un type boolĂ©en, les dĂ©veloppeurs (ou leurs ORM) l’émulent frĂ©quemment avec un entier qu’ils mettront Ă  0 pour FALSE et Ă  1 pour TRUE (alternativement, on rencontre aussi des chaĂźnes avec Y ou N). Un systĂšme de migration ne saura pas dĂ©tecter si cette colonne de type numeric est, pour le dĂ©veloppeur, un boolĂ©en ou une valeur entiĂšre. Du coup, le systĂšme de migration utilisera le typage de la colonne, Ă  savoir entier. Or, un ORM, cherchera un boolĂ©en parce que le code applicatif indiquera un boolĂ©en avant comme aprĂšs la migration. Cela provoquera une erreur sur PostgreSQL, comme le montre l’exemple suivant :

dev2=# CREATE TABLE t1 (c1 int);
CREATE TABLE
dev2=# INSERT INTO t1 VALUES (true);
ERROR:  column "id" is of type integer but expression is of type boolean
LINE 1: insert into t1 values (true);
                               ^
HINT:  You will need to rewrite or cast the expression.
dev2=# INSERT INTO t1 VALUES ('t');
ERROR:  invalid input syntax for integer: "t"
LINE 1: insert into t1 values ('t');
                               ^
dev2=# CREATE TABLE t2 (c1 boolean);
CREATE TABLE
dev2=# INSERT INTO t2 VALUES (true);
INSERT 0 1
dev2=# INSERT INTO t2 VALUES ('f');
INSERT 0 1
dev2=# SELECT * FROM t2;
 c1
---
 t
 f
(2 rows)

Différences sur les types binaires

  • 2 implĂ©mentations diffĂ©rentes sous PostgreSQL
    • large objects et fonctions lo_*
    • bytea

L’implĂ©mentation des types binaires sur PostgreSQL est trĂšs particuliĂšre. De plus, elle est double, dans le sens oĂč vous avez deux moyens d’importer et d’exporter des donnĂ©es binaires dans PostgreSQL.

La premiĂšre, et plus ancienne, implĂ©mentation concerne les Large Objects. Cette implĂ©mentation dispose d’une API spĂ©cifique. Il ne s’agit pas Ă  proprement parler d’un type de donnĂ©es. Il faut passer par des routines stockĂ©es internes qui permettent d’importer, d’exporter, de supprimer, de lister les Large Objects. AprĂšs l’import d’un Large Object, vous rĂ©cupĂ©rez un identifiant que vous pouvez stocker dans une table utilisateur (gĂ©nĂ©ralement dans une colonne de type OID). Vous devez utiliser cet identifiant pour traiter l’objet en question (export, suppression, etc.). Cette implĂ©mentation a de nombreux dĂ©fauts, qui fait qu’elle est rarement utilisĂ©e. Parmi les dĂ©fauts, notons que la suppression d’une ligne d’une table utilisateur rĂ©fĂ©rençant un Large Object ne supprime pas le Large Object rĂ©fĂ©rencĂ©. Notons aussi qu’il est bien plus difficile d’interagir et de maintenir une table systĂšme. Notons enfin que la sauvegarde avec pg_dump est plus complexe et plus longue si des Larges Objects sont dans la base Ă  sauvegarder. Son principal avantage sur la deuxiĂšme implĂ©mentation est la taille maximale d’un Large Object : 4 To.

La deuxiĂšme implĂ©mentation est un type de donnĂ©es appelĂ© bytea. Comme toutes les colonnes dans PostgreSQL, sa taille maximale est 1 Go, ce qui est infĂ©rieur Ă  la taille maximale d’un Large Object. Cependant, c’est son seul dĂ©faut.

La facilitĂ© d’insertion et de lecture d’un champ binaire dĂ©pend du client et du langage, et peut nĂ©cessiter encodage et dĂ©codage. À part cela un bytea est un champ comme un autre.

Bien que l’implĂ©mentation des Large Objects est en perte de vitesse Ă  cause des nombreux inconvĂ©nients inhĂ©rents Ă  son implĂ©mentation, elle a Ă©tĂ© l’objet d’amĂ©liorations sur les derniĂšres versions de PostgreSQL : gestion des droits de lecture ou Ă©criture des Large Objects, notion de propriĂ©taire d’un Large Object, limite de taille relevĂ©e Ă  4 To. Elle n’est donc pas obsolĂšte.


Différences sur les types dates

  • PostgreSQL :
    • date : jour
    • time : heure seule
    • timestamp : date + heure (alias timestamptz)
  • Fuseaux horaires
    • YYYY-MM-DD HH24:MI:SS.mmmmmmm+TZ (conforme SQL)
  • Type interval sous PostgreSQL

Oracle a tendance Ă  mĂ©langer un peu tous les types dates. Ce n’est pas le cas au niveau de PostgreSQL. Pour lui, une colonne de type date contient seulement une date, il n’y a pas d’heure ajoutĂ©e. Une colonne de type time au niveau de PostgreSQL contient seulement un horodatage (heure, minute, seconde, milliseconde), mais pas de date.

Par défaut, PostgreSQL intÚgre le fuseau horaire dans le type timestamp with time zone (alias timestamptz). Le stockage est fait en UTC, mais la restitution dépend du fuseau horaire indiqué par le client.

Bien que le type timestamp without time zone soit aussi disponible (et malheureusement alias de timestamp), il est chaudement conseillĂ© de ne travailler qu’avec le type timestamptz pour faciliter les conversions de fuseaux horaires. Il n’y a mĂȘme pas de pĂ©nalitĂ© en taille du champ.

La conversion se fait automatiquement à l’affichage dans le fuseau horaire courant (selon le paramùtre timezone dans la session). Par exemple :

postgres=# SHOW timezone;
   TimeZone
--------------
 Europe/Paris
(1 ligne)

postgres=# CREATE TABLE moments (t timestamptz) ;
CREATE TABLE

postgres=# SELECT now() ;
              now
-------------------------------
 2021-05-10 18:41:25.497356+02
(1 ligne)

postgres=# INSERT INTO moments SELECT now() ;
INSERT 0 1

postgres=# SET timezone TO 'America/New_York' ;
SET

postgres=# SELECT * FROM moments ;
               t
-------------------------------
 2021-05-10 12:41:21.914092-04
(1 ligne)

postgres=# SET timezone TO 'Asia/Katmandu' ;
SET

postgres=# SELECT * FROM moments ;
                t
----------------------------------
 2021-05-10 22:26:21.914092+05:45
(1 ligne)

PostgreSQL fournit un type interval trÚs pratique pour les calculs temporels :

postgres=# SELECT t + interval '1h' FROM moments ;
             ?column?
----------------------------------
 2021-05-10 23:26:21.914092+05:45

Différences sur les fonctions temporelles

  • SYSDATE
    • retourne la date et l’heure courante, sans timezone
    • Ă©quivalent avec PostgreSQL : localtimestamp
  • PostgreSQL ne propose pas de fonctions add_months, etc.
  • NLS_DATE_FORMAT (TO_CHAR et TO_DATE)
    • configuration DateStyle

Le mot clĂ© SYSDATE est trĂšs frĂ©quent pour gĂ©nĂ©rer une valeur horodatĂ©e avec Oracle. Son Ă©quivalent direct avec PostgreSQL est localtimestamp, qui correspond Ă  la date et l’heure de la timezone du client mais sans que cette timezone n’y soit renseignĂ©e. Il s’agit d’une de type timestamp without timezone.

PostgreSQL implĂ©mente d’autres fonctions pour gĂ©nĂ©rer les valeurs pour chaque type de date Ă  l’horloge actuelle. Il existe par ailleurs la fonction now() qui remplace frĂ©quemment la valeur current_timestamp.

select pg_typeof(localtimestamp) AS localtimestamp,
       pg_typeof(localtime) AS localtime,
       pg_typeof(current_timestamp) AS current_timestamp,
       pg_typeof(current_time) AS current_time,
       pg_typeof(current_date) AS current_date \gx
-[ RECORD 1 ]-----+----------------------------
localtimestamp    | timestamp without time zone
localtime         | time without time zone
current_timestamp | timestamp with time zone
current_time      | time with time zone
current_date      | date

PostgreSQL ne dispose pas de fonctions pour l’ajout ou la soustraction d’une date Ă  une autre. Le type interval y remĂ©die et permet d’aller aussi loin, voire plus, que ne le propose Oracle.

SELECT current_date + interval '3 days';

SELECT current_date + interval '1 days' * 3;

SELECT (now() - '2014-01-01') * 2 + now()

Exemples :

Quel est le premier jour de la premiĂšre semaine de l’annĂ©e ?

SELECT date '2014-01-04' - interval '1 day' *
    (extract('dow' from date '2014-01-04') - 1);

Quel est le premier jour de l’annĂ©e courante ?

SELECT (date_trunc('year', now()) + interval '3 days') - interval '1 day' *
    (extract('dow' from (date_trunc('year', now()) + interval '3 days')) - 1);

Pour Oracle, le format défini par NLS_DATE_FORMAT détermine le format des dates qui sera utilisé pour la sortie des fonctions TO_CHAR() et TO_DATE(). Avec PostgreSQL, cela dépend du format défini par la variable de configuration DateStyle (par défaut ISO, DMY).


Différences sur les types spécialisés

PostgreSQL fournit de nombreux types de données spécialisés :

  • timestamps et intervalles, avec opĂ©rations arithmĂ©tiques
  • Adressage IP (CIDR), avec opĂ©rateurs de masquage
  • Grande extensibilitĂ© des types : il est trĂšs facile d’en rajouter un nouveau
    • PERIOD
    • ip4r

  • Nombreuses extensions
    • PostGIS

L’un des gros avantages de PostgreSQL est son extensibilitĂ©. Le mĂ©canisme des extensions permet de rajouter des types spĂ©cialisĂ©s dans un domaine. Le plus bel exemple est PostGIS, une extension dĂ©diĂ©e aux objets gĂ©ographiques.

Mais mĂȘme sans cela, PostgreSQL propose de nombreux types natifs qui vont bien au-delĂ  des types habituels. Ce sont des types mĂ©tiers, pour le rĂ©seau, la gĂ©omĂ©trie, la gĂ©ographie, la gestion du temps, la gestion des intervalles de valeurs, etc.

Il est donc tout Ă  fait possible d’amĂ©liorer une application en passant sur des types spĂ©cialisĂ©s de PostgreSQL.


Différences de syntaxes

  • Expressions conditionnelles DECODE et NVL
  • ConcatĂ©nation de chaĂźnes avec NULL
  • Pseudo-colonne ROWNUM
  • Jointures
  • OpĂ©rateurs ensemblistes
  • HiĂ©rarchies

Cette partie s’attardera sur les diffĂ©rences notables entre Oracle et PostgreSQL dans la rĂ©daction de requĂȘtes complexes avec le langage SQL.


DECODE

Équivalent de la clause CASE du standard

CASE expr
  WHEN valeur1 THEN valeur_retour1
  WHEN valeur2 THEN valeur_retour2
  ELSE valeur_retour3
END
CASE
  WHEN expr1 THEN valeur_retour1
  WHEN expr2 THEN valeur_retour2
  ELSE valeur_retour3
END

La fonction DECODE d’Oracle est un Ă©quivalent propriĂ©taire de la clause CASE, qui est normalisĂ©e. Oracle supporte CASE mais DECODE est souvent utilisĂ© par habitude.

La construction suivante utilise la fonction DECODE :

SELECT emp_name,
       decode(trunc (( yrs_of_service + 3) / 4), 0, 0.04,
                                          1, 0.04,
                                          0.06) as perc_value
  FROM employees;

Cette construction doit ĂȘtre rĂ©Ă©crite de cette façon :

SELECT emp_name,
       CASE WHEN trunc(yrs_of_service + 3) / 4 = 0 THEN 0.04
            WHEN trunc(yrs_of_service + 3) / 4 = 1 THEN 0.04
            ELSE 0.06
       END
  FROM employees;

Cet autre exemple :

DECODE("user_status",'active',"username",NULL)

sera transposé de cette façon :

CASE WHEN user_status='active' THEN username ELSE NULL END

Attention aux commentaires entre le WHEN et le THEN qui ne sont pas supportés par PostgreSQL.


NVL

  • Retourne le premier argument non NULL
SELECT NVL(description, description_courte, '(aucune)') FROM articles;
  • Équivalent de la norme SQL : COALESCE
SELECT COALESCE(description, description_courte, '(aucune)') FROM articles;

La fonction NVL d’Oracle est encore souvent utilisĂ©e, bien que la fonction normalisĂ©e COALESCE soit Ă©galement implĂ©mentĂ©e. Ces deux fonctions retournent le premier argument qui n’est pas NULL. Bien Ă©videmment, PostgreSQL n’implĂ©mente que la fonction normalisĂ©e COALESCE. Un simple remplacement de l’appel de NVL par un appel Ă  COALESCE est suffisant.

Ainsi, la requĂȘte suivante :

SELECT NVL(description, description_courte, '(aucune)') FROM articles;

se verra portée facilement de cette façon :

SELECT COALESCE(description, description_courte, '(aucune)') FROM articles;

Concaténation avec NULL

Changement de comportement de l’opĂ©rateur ||

  • Rappel : pour Oracle, NULL Ă©quivaut Ă  une chaĂźne vide ''
  • Pour PostgreSQL : SELECT 'chaĂźne' || NULL Ă©quivaut Ă  NULL
  • RĂ©Ă©critures possibles :
SELECT 'chaĂźne' || COALESCE(null, '');
SELECT CONCAT('chaĂźne', null);

Du fait de la spĂ©cificitĂ© du type VARCHAR2 d’Oracle, la concatĂ©nation d’une valeur NULL dans une chaĂźne de caractĂšres ne pose pas de problĂšme, contrairement Ă  PostgreSQL. Le standard SQL dĂ©finit en effet que pour la plupart des fonctions, un paramĂštre NULL entraĂźne un rĂ©sultat NULL (on parle de fonctions STRICT dans PostgreSQL).

Dans le cas présent, une valeur NULL dans une opération de concaténation sera propagée au résultat : le résultat sera une chaßne NULL et non la chaßne de caractÚres attendue.

-- avec Oracle, une valeur NULL ne pose pas de problĂšme
SELECT 'nom de l''employé: ' || last_name FROM employees;

-- avec PostgreSQL, la mĂȘme opĂ©ration retourne NULL, il faut forcer
-- le remplacement d'une valeur nulle par un espace vide avec COALESCE
SELECT $$nom de l'employé: $$ || COALESCE(last_name, '') FROM employees;

Une autre solution consiste Ă  employer la mĂ©thode CONCAT pour s’affranchir de l’opĂ©rateur || et de la fonction COALESCE :

-- avec PostgreSQL, le CONCAT retourne la valeur attendue
SELECT CONCAT($$nom de l'employé: $$, last_name) FROM employees;

ROWNUM

  • Pseudo-colonne Oracle
  • NumĂ©rote les lignes du rĂ©sultat
    • parfois utiliser pour limiter le rĂ©sultat

Oracle propose une pseudo-colonne ROWNUM qui permet de numĂ©roter les lignes du rĂ©sultat d’une requĂȘte SQL. La clause ROWNUM peut ĂȘtre utilisĂ©e soit pour numĂ©roter les lignes de l’ensemble retournĂ© par la requĂȘte. Elle peut aussi ĂȘtre utilisĂ©e pour limiter l’ensemble retournĂ© par une requĂȘte.


Numéroter les lignes

  • ROWNUM n’existe pas dans PostgreSQL
    • row_number() OVER ()
    • attention si ORDER BY

Dans le premier cas, Ă  savoir numĂ©roter les lignes de l’ensemble retournĂ© par la requĂȘte, il faut rĂ©Ă©crire la requĂȘte pour utiliser la fonction de fenĂȘtrage row_number(). Bien qu’Oracle prĂ©conise d’utiliser la fonction normalisĂ©e row_number(), il est frĂ©quent de trouver ROWNUM dans une requĂȘte issue d’une application s’appuyant sur une ancienne version d’Oracle :

SELECT ROWNUM, * FROM employees;

La requĂȘte sera rĂ©Ă©crite de la façon suivante :

SELECT ROW_NUMBER() OVER () AS rownum, * FROM employees;

Il faut toutefois faire attention Ă  une clause ORDER BY dans une requĂȘte employant ROWNUM pour numĂ©roter les lignes retournĂ©es par une requĂȘte. En effet, le tri commandĂ© par ORDER BY est rĂ©alisĂ© aprĂšs l’ajout de la pseudo-colonne ROWNUM. Il faudra vĂ©rifier le rĂ©sultat de la requĂȘte sous Oracle et PostgreSQL pour vĂ©rifier qu’elles retourneront des rĂ©sultats identiques.

La clause WITH ORDINALITY de PostgreSQL 9.4 permet de numĂ©roter les lignes de rĂ©sultat d’un appel de fonction.


Limiter le résultat

  • Retourne les dix premiĂšres lignes de rĂ©sultats :
    • WHERE ROWNUM < 11
  • PostgreSQL propose l’ordre LIMIT xx :
SELECT *
  FROM employees
 LIMIT 10;

Pour limiter l’ensemble retournĂ© par une requĂȘte, il faut supprimer les prĂ©dicats utilisant ROWNUM dans la clause et les transformer en couple LIMIT/OFFSET.

La requĂȘte suivante retourne les 10 premiĂšres lignes de la table employees sous Oracle :

SELECT *
  FROM employees
 WHERE ROWNUM < 11;

Elle sera rĂ©Ă©crite de la façon suivante lors du portage de la requĂȘte pour PostgreSQL :

SELECT *
  FROM employees
 LIMIT 10;

ROWNUM et ORDER BY

  • Oracle effectue le tri aprĂšs l’ajout de ROWNUM
  • PostgreSQL applique le tri avant de limiter le rĂ©sultat
  • RĂ©sultats diffĂ©rents

De la mĂȘme façon que prĂ©cĂ©demment, Oracle effectuera le tri commandĂ© par ORDER BY aprĂšs l’ajout de la pseudo-colonne ROWNUM, comme le montre le plan d’exĂ©cution d’une requĂȘte similaire Ă  l’exemple donnĂ© plus haut :

Operation       Options    Filter Predicates
SELECT STATEMENT
SORT            ORDER BY
COUNT           STOPKEY    ROWNUM<5
TABLE ACCESS    FULL

Au contraire, PostgreSQL va appliquer le tri avant la limitation du rĂ©sultat. Lorsque PostgreSQL rencontre une clause LIMIT et un tri avec ORDER BY, il appliquera d’abord le tri avant de limiter le rĂ©sultat.

test=# EXPLAIN SELECT * FROM t1 ORDER BY col DESC LIMIT 10;
                          QUERY PLAN
---------------------------------------------------------------
 Limit  (cost=4.16..4.19 rows=10 width=4)
   ->  Sort  (cost=4.16..4.41 rows=100 width=4)
         Sort Key: col
         ->  Seq Scan on t1  (cost=0.00..2.00 rows=100 width=4)

Si une requĂȘte Oracle est Ă©crite de maniĂšre aussi simple, il conviendra de la rĂ©Ă©crire de la façon suivante :

SELECT r.*
  FROM (SELECT *
          FROM t1
         LIMIT 10) r
 ORDER BY col

Il faudra nĂ©anmoins se poser la question de la pertinence de cette requĂȘte car le rĂ©sultat n’est pas nĂ©cessairement celui attendu :

NĂ©anmoins, pour palier ce comportement de l’optimiseur Oracle, les dĂ©veloppeurs ont souvent Ă©crit ce genre de requĂȘte en utilisant une sous-requĂȘte, telle que la suivante :

SELECT ROWNUM, r.*
  FROM (SELECT *
          FROM t1
         ORDER BY col) r
 WHERE ROWNUM BETWEEN 1 AND 10;

Cette requĂȘte serait simplifiĂ©e de cette façon une fois migrĂ©e vers PostgreSQL :

SELECT *
  FROM t1
 ORDER BY col
 LIMIT 10;

Jointures

  • Jointures internes
    • FROM tab1, tab2 WHERE tab1.col = tab2.col
    • FROM tab1 JOIN tab2 ON (tab1.col = tab2.col)

Le SGBD Oracle supporte la syntaxe normalisĂ©e d’écriture des jointures seulement depuis la version 9i. Auparavant, les jointures Ă©taient exprimĂ©es telle que le dĂ©finissait la premiĂšre version de la norme SQL, avec une notation propriĂ©taire pour la gestion des jointures externes. PostgreSQL ne supporte pas cette notation propriĂ©taire, mais supporte parfaitement la notation portĂ©e par la norme SQL.

La requĂȘte suivante peut ĂȘtre conservĂ©e telle qu’elle est Ă©crite :

SELECT nom, prenom, titre
  FROM auteurs a , auteurs_livres al, livres l
 WHERE a.id_auteur = al.ref_auteur
   AND al.ref_livre = l.id_livre;

Cependant, cette syntaxe ne permet pas d’écrire de jointure externe. Il est donc recommandĂ© d’utiliser systĂ©matiquement la nouvelle notation, qui est aussi bien plus lisible dans le cas oĂč des jointures simples et externes sont mĂ©langĂ©es :

SELECT nom, prenom, titre
  FROM auteurs a
  JOIN auteurs_livres al ON a.id_auteur = al.ref_auteur
  JOIN livres l ON l.id_livre = al.ref_livre;

Jointures externes

  • Syntaxe (+) d’Oracle historique
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN

Le SGBD Oracle utilise la notation (+) pour dĂ©crire le cĂŽtĂ© oĂč se trouvent les valeurs NULL.

Pour une jointure Ă  gauche, l’annotation (+) serait placĂ©e du cĂŽtĂ© droit (et inversement pour une jointure Ă  droite). Cette forme n’est pas supportĂ©e par PostgreSQL. Il faut donc rĂ©Ă©crire les jointures avec la notation normalisĂ©e : LEFT OUTER JOIN ou LEFT JOIN pour une jointure Ă  gauche et RIGHT OUTER JOIN ou RIGHT JOIN pour une jointure Ă  droite.

La requĂȘte suivante, Ă©crite pour Oracle et qui comporte une jointure Ă  gauche :

SELECT nom, prenom, titre
  FROM auteurs a , auteurs_livres al, livres l
 WHERE a.id_auteur = al.ref_auteur(+)
   AND al.ref_livre = l.id_livre(+);

Deviendra :

SELECT nom, prenom, titre
  FROM auteurs a
  LEFT JOIN auteurs_livres al ON a.id_auteur = al.ref_auteur
  LEFT JOIN livres l ON l.id_livre = al.ref_livre;

De la mĂȘme façon, la requĂȘte suivante comporte une jointure Ă  droite :

SELECT titre, nom, prenom
  FROM auteurs a , auteurs_livres al, livres l
 WHERE a.id_auteur(+) = al.ref_auteur
   AND al.ref_livre(+) = l.id_livre;

et nĂ©cessite d’ĂȘtre rĂ©Ă©crite de la maniĂšre suivante :

SELECT titre, nom, prenom
  FROM auteurs a
  JOIN auteurs_livres al ON a.id_auteur = al.ref_auteur
  RIGHT JOIN livres l ON l.id_livre = al.ref_livre;

La norme ANSI apporte la syntaxe FULL OUTER JOIN pour renvoyer toutes les lignes jointes entre deux tables, ainsi que les lignes sans correspondances à gauche comme à droite.

Dans les versions prĂ©cĂ©dant la version 9i d’Oracle, une jointure externe complĂšte (FULL OUTER JOIN) devait ĂȘtre exprimĂ©e Ă  l’aide d’un UNION entre une jointure Ă  gauche et une jointure Ă  droite. L’exemple suivant implĂ©mente une jointure externe complĂšte :

SELECT nom, prenom, titre
  FROM auteurs a , auteurs_livres al, livres l
 WHERE a.id_auteur = al.ref_auteur(+)
   AND al.ref_livre = l.id_livre(+)
UNION ALL
SELECT nom, prenom, titre
  FROM auteurs a , auteurs_livres al, livres l
 WHERE a.id_auteur(+) = al.ref_auteur
   AND al.ref_livre(+) = l.id_livre
   AND a.id_auteur IS NULL;

Cette requĂȘte doit ĂȘtre rĂ©Ă©crite et sera par ailleurs simplifiĂ©e de la façon suivante :

SELECT nom, prenom, titre
  FROM auteurs a
  LEFT JOIN auteurs_livres al ON a.id_auteur = al.ref_auteur
  FULL OUTER JOIN livres l ON l.id_livre = al.ref_livre;

Produit cartésien

  • FROM t1, t2;
  • FROM t1 CROSS JOIN t2

Un produit cartĂ©sien peut ĂȘtre exprimĂ© de la façon suivante dans Oracle et PostgreSQL :

SELECT *
  FROM t1, t2;

NĂ©anmoins, la notation normalisĂ©e est moins ambigĂŒe et montre clairement l’intention de faire un produit cartĂ©sien :

SELECT *
  FROM t1
  CROSS JOIN t2;

Opérateurs ensemblistes

  • UNION / UNION ALL
  • INTERSECT
  • EXCEPT
    • Ă©quivalent de MINUS

L’opĂ©rateur ensembliste MINUS est Ă  transposer en EXCEPT pour PostgreSQL. Les autres opĂ©rateurs ensemblistes UNION, UNION ALL et INSERSECT ne nĂ©cessitent pas de transposition.

Ainsi, la requĂȘte suivante retourne les produits de l’inventaire qui n’ont pas fait l’objet d’une commande. Elle est exprimĂ©e ainsi pour Oracle :

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items
ORDER BY product_id;

La requĂȘte sera transposĂ© de la façon suivante pour PostgreSQL :

SELECT product_id FROM inventories
EXCEPT
SELECT product_id FROM order_items
ORDER BY product_id;

Hiérarchies

  • Explorer un arbre hiĂ©rarchique
    • CONNECT BY Oracle
    • WITH RECURSIVE PostgreSQL

Oracle propose historiquement la fonction CONNECT BY qui permet d’explorer un arbre hiĂ©rarchique. Cette fonction spĂ©cifique Ă  Oracle possĂšde des fonctionnalitĂ©s avancĂ©es comme la dĂ©tection de cycle et propose des pseudos-colonnes comme le niveau de la hiĂ©rarchie et la construction d’un chemin.

Il n’existe pas de clause directement Ă©quivalente dans PostgreSQL, aussi un travail important de portage doit ĂȘtre rĂ©alisĂ© pour porter les requĂȘtes utilisant cette clause.


Syntaxe CONNECT BY

  • START WITH
    • condition de dĂ©part
  • CONNECT BY PRIOR
    • lien hiĂ©rarchique
SELECT empno, ename, job, mgr
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr

Soit la requĂȘte SQL suivante qui explore la hiĂ©rarchie de la table emp. La colonne mgr de cette table dĂ©signe le responsable hiĂ©rarchique d’un employĂ©. Si elle vaut NULL, alors la personne est au sommet de la hiĂ©rarchie (START WITH mgr IS NULL). Le lien avec l’employĂ© et son responsable hiĂ©rarchique est construit avec la clause CONNECT BY PRIOR empno = mgr qui indique que la valeur de la colonne mgr correspond Ă  l’identifiant empno du niveau de hiĂ©rarchie prĂ©cĂ©dent.

SELECT empno, ename, job, mgr
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr

Le portage de cette requĂȘte est rĂ©alisĂ© Ă  l’aide d’une requĂȘte rĂ©cursive (WITH RECURSIVE). La rĂ©cursion est initialisĂ©e dans une premiĂšre requĂȘte qui rĂ©cupĂšre les lignes qui correspondent Ă  la condition de la clause START WITH de la requĂȘte prĂ©cĂ©dente : mgr IS NULL. La rĂ©cursion continue ensuite avec la requĂȘte suivante qui rĂ©alise une jointure entre la table emp et la vue virtuelle emp_hierarchy qui est dĂ©finie par la clause WITH RECURSIVE. La condition de jointure correspond Ă  la clause CONNECT BY. La vue virtuelle emp_hierarchy a pour alias prior pour mieux reprĂ©senter la transposition de la clause CONNECT BY.

La requĂȘte rĂ©cursive pour PostgreSQL serait alors Ă©crite de la façon suivante :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr) AS (
SELECT empno, ename, job, mgr
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
)
SELECT * FROM emp_hierarchy;

Il faudra nĂ©anmoins faire attention Ă  l’ordre des lignes qui sera diffĂ©rent avec la requĂȘte WITH RECURSIVE. En effet, Oracle utilise un algorithme depth-first dans son implĂ©mentation du CONNECT BY. Ainsi, il explorera d’abord chaque branche avant de passer Ă  la suivante. L’implĂ©mentation WITH RECURSIVE est de type breadth-first qui explore chaque niveau de hiĂ©rarchie avant de descendre.

Il est possible de retrouver l’ordre de tri d’une requĂȘte CONNECT BY pour une version antĂ©rieure Ă  la 11g d’Oracle en triant sur une colonne path, telle qu’elle est construite pour Ă©muler la clause SYS_CONNECT_BY_PATH :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr, path) AS (
SELECT empno, ename, job, mgr, ARRAY[ename::text] AS path
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr, prior.path
       || emp.ename::text AS path
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
)
SELECT empno, ename, job FROM emp_hierarchy AS emp
ORDER BY path

Si vous utilisez Oracle 11g, la requĂȘte retournera quoi qu’il en soit les rĂ©sultats dans un ordre diffĂ©rent.


WITH RECURSIVE

WITH RECURSIVE hierarchie AS (
condition de départ
UNION ALL
clause de récursion
)
SELECT * FROM hierarchie

Niveau de hiérarchie

  • LEVEL donne le niveau de hiĂ©rarchie
  • Condition de dĂ©part
   1 AS level
  • Clause de rĂ©cursion
   prior.level + 1

La clause LEVEL permet d’obtenir le niveau de hiĂ©rarchie d’un Ă©lĂ©ment.

SELECT empno, ename, job, mgr, level
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr

Le portage de la clause LEVEL est facile. La requĂȘte d’initialisation de la rĂ©cursion initialise la colonne level Ă  1. La requĂȘte de rĂ©cursion effectue ensuite une incrĂ©mentation de cette colonne pour chaque niveau de hiĂ©rarchie exploré :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr, level) AS (
SELECT empno, ename, job, mgr, 1 AS level
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr, prior.level + 1
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
)
SELECT * FROM emp_hierarchy;

Chemin de hiérarchie

  • niveau 1/niveau 2/niveau 3
  • Condition de dĂ©part
    • niveau initial AS path
  • Clause de rĂ©cursion
    • concatĂšne le niveau prĂ©cĂ©dent avec le path
    • prior.path || niveau courant

La clause SYS_CONNECT_BY_PATH permet d’obtenir un chemin oĂč chaque Ă©lĂ©ment est sĂ©parĂ© de l’autre par un caractĂšre donnĂ©. Par exemple, la requĂȘte suivante indique qui sont les diffĂ©rents responsables d’un employĂ© de cette façon :

SELECT empno, ename, job, mgr, SYS_CONNECT_BY_PATH(ename, '/') AS path
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr

Le portage de la clause SYS_CONNECT_BY_PATH est Ă©galement assez facile. La requĂȘte d’initialisation de la rĂ©cursion construit l’élĂ©ment racine : '/' || ename AS path. La requĂȘte de rĂ©cursion rĂ©alise quant Ă  elle une concatĂ©nation entre le path rĂ©cupĂ©rĂ© de la prĂ©cĂ©dente itĂ©ration et l’élĂ©ment Ă  concatĂ©ner : prior.path || '/' || emp.ename :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr, path) AS (
SELECT empno, ename, job, mgr, '/' || ename AS path
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr, prior.path || '/' || emp.ename
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
)
SELECT * FROM emp_hierarchy

Une autre façon de faire est d’utiliser un tableau pour stocker le chemin le temps de la rĂ©cursion, puis de construire la reprĂ©sentation textuelle de ces chemins au moment de la sortie des rĂ©sultats. À noter la conversion de la valeur de ename en type text pour chaque Ă©lĂ©ment ajoutĂ© dans le tableau path. Cette variante peut ĂȘtre utile pour l’émulation de la clause NOCYCLE comme vu plus bas :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr, path) AS (
SELECT empno, ename, job, mgr, ARRAY[ename::text] AS path
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr, prior.path ||
       emp.ename::text AS path
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
)
SELECT empno, ename, job, array_to_string(path, '/') AS path
FROM emp_hierarchy AS emp

DĂ©tection des cycles

  • Équivalent de NOCYCLE
  • Tableau contenant les Ă©lĂ©ments
    • pseudo-colonne cycle
    • element = ANY (tableau) AS cycle
    • WHERE cycle = false

La requĂȘte Oracle suivante :

SELECT empno, ename, job, mgr
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY NOCYCLE PRIOR empno = mgr

sera transposée pour PostgreSQL de la façon suivante :

WITH RECURSIVE emp_hierarchy (empno, ename, job, mgr, path, cycle) AS (
SELECT empno, ename, job, mgr, ARRAY[ename::text] AS path, false AS cycle
  FROM emp
 WHERE mgr IS NULL
UNION ALL
SELECT emp.empno, emp.ename, emp.job, emp.mgr, prior.path ||
       emp.ename::text AS path, emp.ename = ANY(prior.path) AS cycle
  FROM emp
  JOIN emp_hierarchy prior ON (emp.mgr = prior.empno)
 WHERE cycle = false
)
SELECT empno, ename, job, mgr
  FROM emp_hierarchy AS emp
 WHERE cycle = false;

Common Table Expressions

  • Syntaxe quasiment identique
  • Attention Ă  la rĂ©cursion
    • WITH RECURSIVE obligatoire dans PostgreSQL

Un article Ă©crit par Lucas Jellema montre les Ă©volutions d’Oracle 11gR2 concernant les requĂȘtes rĂ©cursives. Les diffĂ©rents exemples montrent que les requĂȘtes Ă©crites utilisent les CTE au lieu du CONNECT BY qui fait partie seulement du dialecte SQL Oracle. L’article est disponible Ă  cette adresse.

Si l’on exĂ©cute la seconde requĂȘte donnĂ©e en exemple (la premiĂšre employant CONNECT BY directement sur PostgreSQL, on obtient le message d’erreur suivant :

DÉTAIL : There is a WITH item named "employees", but it cannot be referenced
         from this part of the query.
ASTUCE : Use WITH RECURSIVE, or re-order the WITH items to remove forward
         references.

Pour corriger ce problĂšme, il suffit d’ajouter la clause RECURSIVE, comme l’indique tout simplement le message d’erreur et la requĂȘte pourra ĂȘtre exĂ©cutĂ©e sans difficultĂ©.


Transactions

  • Les transactions ne sont pas dĂ©marrĂ©es automatiquement
    • BEGIN
    • sauf avec JDBC (BEGIN cachĂ©)
  • Toute erreur non gĂ©rĂ©e dans une transaction entraĂźne son annulation
    • Oracle revient Ă  l’état prĂ©cĂ©dent de l’ordre en Ă©chec
    • PostgreSQL plus strict de ce point de vue
  • DDL transactionnels

Pour PostgreSQL, si vous souhaitez pouvoir annuler des modifications, vous devez utiliser BEGIN avant d’exĂ©cuter les requĂȘtes de modification. Toute transaction qui commence par un BEGIN doit ĂȘtre validĂ©e avec COMMIT ou annulĂ©e avec ROLLBACK. Si jamais la connexion est perdue entre le serveur et le client, le ROLLBACK est automatique.

Par exemple, si on insĂšre une donnĂ©e dans une table, sans faire de BEGIN avant, et qu’on essaie d’annuler cette insertion, cela ne fonctionnera pas :

dev2=# CREATE TABLE t1(id integer);
CREATE TABLE
dev2=# INSERT INTO t1 VALUES (1);
INSERT 0 1
dev2=# ROLLBACK;
NOTICE:  there is no transaction in progress
ROLLBACK
dev2=# SELECT * FROM t1;
 id
---
  1
(1 row)

Par contre, si on intùgre un BEGIN avant, l’annulation se fait bien :

dev2=# BEGIN;
BEGIN
dev2=# INSERT INTO t1 VALUES (2);
INSERT 0 1
dev2=# ROLLBACK;
ROLLBACK
dev2=# SELECT * FROM t1;
 id
---
  1
(1 row)

Dans PostgreSQL, l’autocommit est un paramĂ©trage du client. Il est possible de le dĂ©sactiver dans psql avec le paramĂštrage \set AUTOCOMMIT off. Le BEGIN deviendra automatique et implicite, et il faudra entrer COMMIT ou ROLLBACK pour terminer la transaction et en ouvrir une nouvelle automatiquement.

De mĂȘme, le pilote JDBC de la communautĂ© PostgreSQL permet de dĂ©sactiver l’autocommit, et rajoutera silencieusement les BEGIN.

Autre diffĂ©rence au niveau transactionnel : il est possible d’intĂ©grer des ordres DDL dans des transactions. Par exemple :

dev2=# BEGIN;
BEGIN
dev2=# CREATE TABLE t2(id integer);
CREATE TABLE
dev2=# INSERT INTO t2 VALUES (1);
INSERT 0 1
dev2=# ROLLBACK;
ROLLBACK
dev2=# INSERT INTO t2 VALUES (2);
ERROR:  relation "t2" does not exist
LINE 1: INSERT INTO t2 VALUES (2);
                    ^

Enfin, quand une transaction est en erreur, vous ne sortez pas de la transaction. Vous devez absolument exécuter un ordre de fin de transaction (COMMIT ou ROLLBACK, peu importe, un ROLLBACK sera exécuté) :

dev2=# BEGIN;
BEGIN
dev2=# INSERT INTO t2 VALUES (2);
ERROR:  relation "t2" does not exist
LINE 1: INSERT INTO t2 VALUES (2);
                    ^
dev2=# INSERT INTO t1 VALUES (2);
ERROR:  current transaction is aborted, commands ignored until
        end of transaction block
dev2=# SELECT * FROM t1;
ERROR:  current transaction is aborted, commands ignored until
        end of transaction block
dev2=# ROLLBACK;
ROLLBACK
dev2=# SELECT * FROM t1;
 id
---
  1
(1 row)

Niveaux d’isolation

  • BEGIN TRANSACTION ISOLATION LEVEL xxxx
    • READ COMMITTED
    • REPEATABLE READ
    • SERIALIZABLE

Il est possible d’indiquer le niveau d’isolation d’une transaction en l’indiquant dans l’ordre d’ouverture d’une transaction :

BEGIN [ WORK | TRANSACTION ] [ mode_transaction [, ...] ]

oĂč mode_transaction est :

ISOLATION LEVEL
    { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

READ UNCOMMITTED est un synonyme de READ COMMITTED sous PostgreSQL, tout comme sous Oracle : les moteurs Ă©tant MVCC, le mode READ UNCOMMITTED n’a pas d’intĂ©rĂȘt (les Ă©crivains ne bloquent pas les lecteurs, les lecteurs ne bloquent pas les Ă©crivains).

Par ailleurs, Oracle et PostgreSQL implĂ©mentent un niveau d’isolation SERIALIZABLE. PostgreSQL implĂ©mente le niveau d’isolation SERIALIZABLE avec des verrous optimistes afin de garantir un meilleur dĂ©bit transactionnel. La plupart des SGBD implĂ©mentent ce niveau d’isolation par le biais de verrous pessimistes, grevant ainsi les performances. Les versions plus anciennes d’Oracle possĂ©daient d’ailleurs un paramĂštre non-documentĂ© SERIALIZABLE pour activer l’emploi de verrous pessimistes, mais il n’est plus supportĂ© depuis Oracle 8.1.6. Ce paramĂštre permet donc d’activer ce mode d’isolation de façon Ă  ce qu’il soit respectueux de la norme, au prix de performances dĂ©gradĂ©es. Dans les versions actuelles, Oracle n’utilise pas de verrou et de ce fait, son implĂ©mentation du niveau d’isolation SERIALIZABLE n’est pas respectueuse de la norme, Ă  la diffĂ©rence de PostgreSQL. Il faut noter Ă©galement que depuis la version 9.1, PostgreSQL est le premier SGBD qui implĂ©mente un mode d’isolation SERIALIZABLE parfaitement respectueux de la norme SQL. Cette fonctionnalitĂ©, issue de travaux de recherches universitaires, est appelĂ©e Serializable Snapshot Isolation et corrige les dĂ©fauts des implĂ©mentations prĂ©cĂ©dentes du niveau SERIALIZABLE.

Oracle permet de positionner le niveau d’isolation des transactions pour une session donnĂ©e, c’est-Ă -dire pour toutes les transactions rĂ©alisĂ©es dans la mĂȘme session.

L’ordre SQL suivant permet de positionner le niveau d’isolation au niveau de la session pour Oracle :

ALTER SESSION SET ISOLATION LEVEL ...;

L’ordre SET SESSION ... permet de rĂ©aliser la mĂȘme chose pour PostgreSQL :

SET SESSION TRANSACTION ISOLATION LEVEL ...;

Pour plus de dĂ©tails sur les niveaux d’isolation, consulter la documentation de PostgreSQL sur l’isolation des transactions.


SAVEPOINT

  • SAVEPOINT
  • RELEASE SAVEPOINT
  • ROLLBACK TO SAVEPOINT

Les SAVEPOINT fonctionnent sans rĂ©gression par rapport au SGBD Oracle. Les verrous acquis avant la mise en place d’un SAVEPOINT ne sont pas relĂąchĂ©s si un SAVEPOINT est relĂąchĂ© par un RELEASE SAVEPOINT ou un ROLLBACK TO SAVEPOINT

La documentation de PostgreSQL met nĂ©anmoins en garde contre la modification de lignes aprĂšs le positionnement d’un SAVEPOINT alors que ces lignes ont Ă©tĂ© verrouillĂ©es par un SELECT .. FOR UPDATE avant le positionnement du SAVEPOINT. En effet, le verrou acquis par le SELECT ... FOR UPDATE peut ĂȘtre relĂąchĂ© au moment du ROLLBACK TO SAVEPOINT. La sĂ©quence suivante d’ordres SQL est donc Ă  Ă©viter :

BEGIN;
SELECT * FROM ma_table WHERE cle = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE ma_table SET ... WHERE cle = 1;
ROLLBACK TO SAVEPOINT s;

Verrous explicites

  • SELECT FOR SHARE/UPDATE
    • quelques subtilitĂ©s
  • LOCK TABLE

Les ordres SELECT FOR UPDATE peuvent nécessiter des adaptations. La syntaxe Oracle est en effet un peu plus riche que celle de PostgreSQL pour ce qui concerne cet ordre SQL.

Oracle propose une syntaxe WAIT et NOWAIT. PostgreSQL ne propose que la clause NOWAIT. La clause WAIT est implicite si NOWAIT n’est pas spĂ©cifiĂ©, il faudra donc la supprimer. La requĂȘte SELECT ... FOR UPDATE WAIT; devient SELECT ... FOR UPDATE;.

En l’état, la clause OF Oracle est incompatible avec le clause OF de PostgreSQL. Cette clause permet d’indiquer la table verrouillĂ©e pour une mise Ă  jour ultĂ©rieure. Seulement, la clause OF d’Oracle dĂ©signe une colonne d’une table, tandis que la clause OF de PostgreSQL dĂ©signe une table.

La clause SKIP LOCKED existe dans PostgreSQL depuis la version 9.5.

Concernant la syntaxe de l’ordre LOCK TABLE d’Oracle est compatible avec celle de PostgreSQL pour les cas gĂ©nĂ©raux. L’ensemble des modes de verrouillage proposĂ©s par Oracle existent tous dans PostgreSQL. On peut noter que PostgreSQL propose plus de type de verrous.

Tout comme pour l’ordre SELECT FOR UPDATE, Oracle propose une syntaxe WAIT et NOWAIT. PostgreSQL ne propose aussi que la clauseNOWAIT. La clause WAIT est implicite si NOWAIT n’est pas spĂ©cifiĂ©, il faudra donc la supprimer. La requĂȘte LOCK TABLE ... WAIT; devient LOCK TABLE ...;.

Les clauses PARTITION et SUBPARTITION ne peuvent cependant pas ĂȘtre reprises. Dans le cas de la mise en Ɠuvre du partitionnement dans PostgreSQL, il faut dĂ©signer la table correspondant Ă  la partition ciblĂ©e par l’acquisition d’un verrou.


Conclusion

  • Oracle et PostgreSQL rĂ©pondent Ă  la mĂȘme norme ISO SQL
    • 
 mais la conversion des requĂȘtes SQL sera nĂ©cessaire
    • le typage des donnĂ©es est bien plus fourni avec PostgreSQL
  • Pour dĂ©tecter les erreurs de syntaxe :
    • faire fonctionner l’application
    • repĂ©rer tous les ordres SQL en erreur (traces applicatives)
    • les corriger
  • Attention aux mots rĂ©servĂ©s

Le langage SQL est commun entre Oracle et PostgreSQL. La norme SQL veille Ă  ce qu’aucun Ă©diteur logiciel ne propose un langage propriĂ©taire. Cependant, le respect strict de cette norme est bien plus important pour PostgreSQL, alors que de son cĂŽtĂ©, Oracle propose des mots clĂ©s spĂ©cifiques tels que NVL, ROWNUM ou CONNECT BY, pour ne citer qu’eux.

Le plus grand soin doit ĂȘtre apportĂ© dans la conversion des types lors d’une migration. Alors qu’un type temporel sera stockĂ© en date sur Oracle, il sera dĂ©clinĂ© en plusieurs sous-ensembles dĂ©finis par la norme avec PostgreSQL. Les types date, time et timestamp sont bien distincts et auront leur propre comportement vis-Ă -vis des opĂ©rateurs arithmĂ©tiques ou de comparaison.

Les types numĂ©riques sont plus nombreux avec PostgreSQL, dans le but de gĂ©rer plus finement le stockage de chaque valeur (2, 4 ou 8 octets selon les dĂ©clinaisons). Il peut y avoir des surprises lors d’une migration oĂč une colonne de type NUMBER sera convertie en numeric dans PostgreSQL alors que les valeurs devraient ĂȘtre traitĂ©es en integer dans la logique applicative.

Le portage des requĂȘtes SQL vers PostgreSQL est donc indispensable pour garantir la meilleure utilisation des fonctionnalitĂ©s de ce moteur. Bien qu’il soit toujours intĂ©ressant de faire une premiĂšre passe dans le code source de votre application pour corriger les cas les plus flagrants, il est nĂ©cessaire ensuite de l’exĂ©cuter sur PostgreSQL et de vĂ©rifier dans les journaux applicatifs les messages d’erreurs qui surviennent.

Un outil comme pgBadger permet de rĂ©cupĂ©rer les erreurs, leur frĂ©quence et les requĂȘtes qui ont causĂ© les erreurs.

Pour récupérer la liste des mots réservés :

SELECT * FROM pg_get_keywords();

Questions

N’hĂ©sitez pas, c’est le moment !


Quiz

Travaux pratiques

Question 1

Les traitements sur les dates

RĂ©Ă©crire pour PostgreSQL la requĂȘte suivante (NB : seule la date du jour nous intĂ©resse, pas les heures) :

SELECT SYSDATE + 1 FROM DUAL;

Exemple de valeur retournée sur Oracle (affichage par défaut sans les heures !) :

SYSDATE+1
---------
14-MAR-14

Faites de mĂȘme avec la requĂȘte :

SELECT add_months(to_date('14-MAR-2014'), 2) FROM DUAL;

Valeur retournée sur Oracle :

ADD_MONTH
---------
14-MAY-14

Question 2

Jointures (+)

MĂȘme si la notation (+) n’est pas recommandĂ©e, il peut rester de nombreux codes utilisant cette notation.

RĂ©Ă©crire le code suivant dans le respect de la norme :

SELECT last_name, first_name, department_name
  FROM employees e, departments d
 WHERE e.employee_id = d.manager_id (+);

Puis ce code :

SELECT city, country_name
  FROM locations l, countries c
 WHERE l.country_id (+) = c.country_id;

Question 3

ROWNUM

RĂ©Ă©crire la requĂȘte suivante utilisant ROWNUM pour numĂ©roter les lignes retournĂ©es :

SELECT ROWNUM, country_name, region_name
  FROM countries c
  JOIN regions r ON (c.region_id = r.region_id);

et cette requĂȘte utilisant ROWNUM pour limiter le nombre de lignes ramenĂ©es :

SELECT country_name, region_name
  FROM countries c
  JOIN regions r ON (c.region_id = r.region_id)
WHERE ROWNUM < 21;

Question 4

Portage de DECODE

La construction suivante utilise la fonction DECODE :

SELECT last_name, job_id, salary,
       DECODE(job_id,
              'PU_CLERK', salary * 1.05,
              'SH_CLERK', salary * 1.10,
              'ST_CLERK', salary * 1.15,
              salary) "Proposed Salary"
  FROM employees
 WHERE job_id LIKE '%_CLERK'
   AND last_name < 'E'
 ORDER BY last_name;

RĂ©Ă©crire cette requĂȘte pour son exĂ©cution sous PostgreSQL.

Autre exemple Ă  convertir :

DECODE("user_status",'active',"username",NULL)

Question 5

FUNCTION

Porter sur PostgreSQL la fonction Oracle suivante :

CREATE OR REPLACE FUNCTION text_length(a CLOB)
  RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN DBMS_LOB.GETLENGTH(a);
END;

Les fonctions sur les chaßnes de caractÚres sont listées dans la documentation : http://docs.postgresql.fr/current/functions-string.html


Question 6

CONNECT BY

RĂ©Ă©crire la requĂȘte suivante Ă  base de CONNECT BY sous Oracle :

 SELECT employee_id, last_name, manager_id
   FROM employees
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
  ORDER BY employee_id;

Cette requĂȘte explore la hiĂ©rarchie de la table employees. La colonne manager_id de cette table dĂ©signe le responsable hiĂ©rarchique d’un employĂ©. Si elle vaut NULL, alors la personne est au sommet de la hiĂ©rarchie comme exprimĂ© par la partie START WITH manager_id IS NULL de la requĂȘte. Le lien avec l’employĂ© et son responsable hiĂ©rarchique est construit avec la clause CONNECT BY PRIOR employee_id = manager_id qui indique que la valeur de la colonne manager_id correspond Ă  l’identifiant employee_id du niveau de hiĂ©rarchie prĂ©cĂ©dent.

Voici le retour de cette requĂȘte sous Oracle :

EMPLOYEE_ID LAST_NAME      MANAGER_ID
----------- -------------- ----------
        100 King
        101 Kochhar               100
        102 De Haan               100
        103 Hunold                102
        104 Ernst                 103
(...)

Pour vous aider, le portage de ce type de requĂȘte se fait Ă  l’aide d’une requĂȘte rĂ©cursive (WITH RECURSIVE) sous PostgreSQL. Pour plus d’informations, voir la documentation : https://docs.postgresql.fr/current/queries-with.html

Travaux pratiques (solutions)

Question 1

Les traitements sur les dates

PostgreSQL connaĂźt les fonctions CURRENT_DATE (date sans heure) et CURRENT_TIMESTAMP (type TIMESTAMP WITH TIME ZONE, soit date avec heure).

Si on ne s’intĂ©resse qu’à la date, la premiĂšre requĂȘte peut simplement ĂȘtre remplacĂ©e par :

-- SELECT SYSDATE + 1 FROM DUAL;
-- devient
SELECT CURRENT_DATE + 1;

Ce qui est Ă©quivalent Ă  :

SELECT CURRENT_DATE + '1 days'::interval;

Pour la seconde il y a un peu plus de travail de conversion :

-- SELECT add_months(to_date('14-MAR-2014'), 2) FROM DUAL;
-- devient
SELECT '2014-03-14'::date + '2 months'::interval;

renvoie :

      ?column?
---------------------
 2014-05-14 00:00:00
(1 ligne)

Pour éliminer la partie heure, il faut forcer le type retourné :

SELECT cast('2014-03-14'::date + '2 months'::interval as date);

ou, ce qui revient au mĂȘme :

SELECT ('2014-03-14'::date + '2 months'::interval)::date;

Question 2

Jointures (+)

La premiĂšre requĂȘte correspond Ă  une jointure de type LEFT OUTER JOIN :

-- SELECT last_name, first_name, department_name
--   FROM employees e, departments d
--  WHERE e.employee_id = d.manager_id (+);
-- devient
SELECT last_name, first_name, department_name
  FROM employees e
  LEFT OUTER JOIN departments d ON e.employee_id = d.manager_id;

et la seconde Ă  une jointure de type RIGHT OUTER JOIN :

-- SELECT city, country_name
--   FROM locations l, countries c
--  WHERE l.country_id (+) = c.country_id;
-- devient
SELECT city, country_name
  FROM locations l
 RIGHT JOIN countries c ON l.country_id = c.country_id;

Question 3

ROWNUM

La requĂȘte permettant la numĂ©rotation des lignes sera rĂ©Ă©crite de la façon suivante :

-- SELECT ROWNUM, country_name, region_name
--   FROM countries c
--   JOIN regions r ON (c.region_id = r.region_id);
-- devient
SELECT row_number() OVER () AS rownum, country_name, region_name
  FROM countries c
  JOIN regions r ON (c.region_id = r.region_id);

La clause OVER () devrait comporter Ă  minima un ORDER BY pour spĂ©cifier l’ordre dans lequel on souhaite avoir les rĂ©sultats. Cela dit, la requĂȘte telle qu’elle est Ă©crite ci-dessus est une transposition fidĂšle de son Ă©quivalent Oracle.

La seconde requĂȘte ramĂšne les 20 premiers Ă©lĂ©ments (arbitrairement, sans tri) :

-- SELECT country_name, region_name
--   FROM countries c
--   JOIN regions r ON (c.region_id = r.region_id)
--  WHERE ROWNUM < 21;
-- devient
SELECT country_name, region_name
  FROM countries c
  JOIN regions r ON (c.region_id = r.region_id)
 LIMIT 20 OFFSET 0;

Pour une requĂȘte pouvant interroger un grand nombre de donnĂ©es, la rĂ©Ă©criture avec OFFSET limitera les performances de PostgreSQL.

Il faudra alors rĂ©Ă©crire la requĂȘte en combinant FETCH FIRST N ROWS ONLY et l’utilisation de la derniĂšre rĂ©gion de la pagination dans la clause WHERE ainsi que dans les colonnes retournĂ©es par le SELECT.

SELECT c.region_id, country_name, region_name
  FROM countries c
  JOIN regions r ON (c.region_id = r.region_id)
 WHERE c.region_id < 100
 ORDER BY region_id DESC
 FETCH FIRST 20 ROWS ONLY;

Question 4

Portage de DECODE

La fonction DECODE d’Oracle est un Ă©quivalent propriĂ©taire de la clause CASE, qui est normalisĂ©e.

SELECT last_name, job_id, salary,
       DECODE(job_id,
              'PU_CLERK', salary * 1.05,
              'SH_CLERK', salary * 1.10,
              'ST_CLERK', salary * 1.15,
              salary) "Proposed Salary"
  FROM employees
 WHERE job_id LIKE '%_CLERK'
   AND last_name < 'E'
 ORDER BY last_name;

Cette construction doit ĂȘtre rĂ©Ă©crite de cette façon :

SELECT last_name, job_id, salary,
       CASE job_id
            WHEN 'PU_CLERK' THEN salary * 1.05
            WHEN 'SH_CLERK' THEN salary * 1.10
            WHEN 'ST_CLERK' THEN salary * 1.15
            ELSE salary
       END AS "Proposed salary"
  FROM employees
 WHERE job_id LIKE '%_CLERK'
   AND last_name < 'E'
 ORDER BY last_name;

RĂ©Ă©criture du second exemple :

-- DECODE("user_status",'active',"username",NULL)
-- devient
CASE WHEN user_status='active' THEN username ELSE NULL END

Question 5

FUNCTION

La fonction Oracle

CREATE OR REPLACE FUNCTION text_length(a CLOB)
  RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN DBMS_LOB.GETLENGTH(a);
END;

peut ĂȘtre rĂ©Ă©crite de la façon suivante en langage PL/pgSQL :

CREATE OR REPLACE FUNCTION text_length (a text) RETURNS integer AS
$$
BEGIN
  RETURN char_length(a);
END
$$
LANGUAGE plpgsql
IMMUTABLE;

ou simplement en SQL :

CREATE OR REPLACE FUNCTION text_length (a text) RETURNS integer AS
$$
SELECT char_length(a);
$$
LANGUAGE sql
IMMUTABLE;

Question 6

CONNECT BY

RĂ©Ă©crire la requĂȘte suivante Ă  base de CONNECT BY sous Oracle :

 SELECT employee_id, last_name, manager_id
   FROM employees
  START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
  ORDER BY employee_id;

La requĂȘte rĂ©cursive pour PostgreSQL serait Ă©crite de la façon suivante :

WITH RECURSIVE employees_hierarchie (employee_id, last_name, manager_id) AS
(
  SELECT employee_id, last_name, manager_id
    FROM employees
   WHERE manager_id IS NULL
   UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id
    FROM employees e
    JOIN employees_hierarchie prior ON (e.manager_id = prior.employee_id)
)
SELECT * FROM employees_hierarchie
 ORDER BY employee_id;

RĂ©sultat :

 employee_id |  last_name  | manager_id
-------------+-------------+------------
         100 | King        |
         101 | Kochhar     |        100
         102 | De Haan     |        100
         103 | Hunold      |        102
         104 | Ernst       |        103

La rĂ©cursion est initialisĂ©e dans une premiĂšre requĂȘte qui rĂ©cupĂšre les lignes correspondant Ă  la condition de la clause START WITH de la requĂȘte prĂ©cĂ©dente : manager_id IS NULL.

La rĂ©cursion continue ensuite avec la requĂȘte suivante qui rĂ©alise une jointure entre la table employees et la vue virtuelle employees_hierarchie qui est dĂ©finie par la clause WITH RECURSIVE. La condition de jointure correspond Ă  la clause CONNECT BY. La vue virtuelle employees_hierarchie a pour alias prior pour mieux reprĂ©senter la transposition de la clause CONNECT BY.