Ce module est une introduction aux migrations de Oracle vers PostgreSQL. Nous y abordons comment gérer sa première migration (quelque soit le SGBD source et destination), des recommandations sur une migration d’Oracle vers PostgreSQL (où nous détaillons les pièges à éviter et les principales différences entre les deux SGBD), une réflexion sur le contenu de la migration et sur le choix de l’outil idoine, et nous finissons avec quelques détails sur des fonctionnalités manquantes ou implémentées différemment côté PostgreSQL qui rendront la migration plus difficile.

La façon dont la première migration va se dérouler est essentielle. C’est sur cette expérience que les autres migrations seront abordées. Si l’expérience a été mauvaise, il est même probable que les migrations prévues après soient repoussées fortement, voire annulées.

Il est donc essentiel de réussir sa première migration. Réussir veut aussi dire bien la documenter, car elle servira de base pour les prochaines migrations : les méthodes employées seront réutilisées, et certainement améliorées. Réussir veut aussi dire la publiciser, au moins en interne, pour que tout le monde sache que ce type de migration est réalisable et qu’une expérience est disponible en interne.

De plus, cette migration va influencer fortement la vision des développeurs et des utilisateurs vis-à-vis de ce SGBD. Réussir la migration veut donc aussi dire réussir à faire apprécier et accepter ce moteur de bases de données. Sans cela, il y a de fortes chances que les prochaines migrations ne soient pas demandées volontairement, ce qui rendra les migrations plus difficiles.

Le premier projet de migration doit être sélectionné avec soin.

S’il est trop simple, il n’aura pas réellement de valeur. Par exemple, dans le cas d’une migration d’une base de 100 Mo, sans routines stockées, sans fonctionnalités avancées, cela ne constituera pas une base qui permettra d’aborder tranquillement une migration d’une base de plusieurs centaines de Go et utilisant des fonctionnalités avancées.

L’inverse est aussi vrai. Un projet trop gros risque d’être un souci. Prenez une base critique de plusieurs To, dotée d’un très grand nombre de routines stockées. C’est un véritable challenge, y compris pour une personne expérimentée. Il y a de fortes chances que la migration soit longue, dure, mal vécue… et possiblement annulée à cause de sa complexité. Ceci aura un retentissement fort sur les prochaines migrations.

Il est préférable de choisir un projet un peu entre les deux : une base conséquence (plusieurs dizaines de Go), avec quelques routines stockées, de la réplication, etc. Cela aura une vraie valeur, tout en étant à portée de main pour une première migration.

Une fois une telle migration réussie, il sera plus simple d’aborder correctement et sans crainte la migration de bases plus volumineuses ou plus complexes.

Il faut aussi ne pas oublier que la migration doit impliquer un groupe entier, pas seulement une personne. Les développeurs, les administrateurs, les équipes de support doivent tous être impliqués dans ce projet, pour qu’ils puissent intégrer les changements quant à l’utilisation de ce nouveau SGBD.

L’équipe du projet de migration doit être interne, même si une aide externe peut être sollicitée. Un chef de projet doit être nommé au sein d’une équipe hétérogène, composée de développeurs, d’administrateurs, de testeurs et d’utilisateurs. Il est à noter que les testeurs sont une partie essentielle de l’équipe.

Même si l’essentiel du projet est porté en interne, il est toujours possible de faire appel à une société externe spécialisée dans ce genre de migrations. Cela permet de gagner du temps sur certaines étapes de la migration pour éviter certains pièges, ou mettre en place l’outil de migration.

Cette migration doit être gérée comme tout autre projet :

• une réunion de lancement ;
• des réunions de suivi ;
• des rapports d’avancements.

De même, ce projet a besoin de ressources, et notamment des serveurs de tests : par exemple un serveur Oracle contenant la base à migrer (mais qui ne soit pas le serveur de production), et un serveur PostgreSQL contenant la base à migrer. Ces deux serveurs doivent avoir la volumétrie réelle de la base de production, sinon les tests de performance n’auront pas vraiment de valeur.

En fait, il faut vraiment que cette migration soit considérée comme un vrai projet, et pas comme un projet au rabais, ce qui arrive malheureusement assez fréquemment. C’est une opération essentielle, et des ressources compétentes et suffisantes doivent être offertes pour la mener à bien.

En soi, passer à PostgreSQL n’est pas une révolution. C’est un moteur de base de données comme les autres, avec un support du SQL (et quelques extensions) et ses fonctionnalités propres. Ce qui change est plutôt l’implémentation, mais, comme nous le verrons dans cette formation, si une fonctionnalité identique n’existe pas, une solution de contournement est généralement disponible.

La majorité des utilisateurs de PostgreSQL vient à PostgreSQL pour faire des économies (sur les coûts de licence). Si jamais une telle migration demandait énormément de changements, ils ne viendraient pas à PostgreSQL. Or la majorité des migrations se passe bien, et les utilisateurs restent ensuite sur PostgreSQL. Les migrations qui échouent sont généralement celles qui n’ont pas été correctement gérées dès le départ (pas de ressources pour le projet, un projet trop gros dès le départ, etc.).

Le passage à un nouveau SGBD est un peu un saut dans l’inconnu pour la majorité des personnes impliquées. Elles connaissent bien un moteur de bases de données et souvent ne comprennent pas pourquoi on veut les faire passer à un autre moteur. C’est pour cela qu’il est nécessaire de les impliquer dès le début du projet et, le cas échéant, de les former. Il est possible d’avoir de nombreuses formations autour de PostgreSQL pour les différents acteurs : chefs de projet, administrateurs de bases de données, développeurs, etc.

De toute façon, les concepts utilisés par PostgreSQL sont très proches des concepts des moteurs SGBD propriétaires. La majorité du temps, il suffit d’adapter les compétences. Il n’est jamais nécessaire de reprendre tout à zéro. La connaissance d’un autre moteur de bases de données permet de passer très facilement à PostgreSQL, ce qui valorise l’équipe.

Contrairement à d’autres projets, le service existe déjà. Les délais sont donc généralement moins importants, ce qui permet de donner du temps aux personnes impliquées dans le projet pour fournir une migration de qualité (et surtout documenter cette opération).

Une migration aura un coût important. Ce n’est pas parce que PostgreSQL est un logiciel libre que tout est gratuit. La mise à disposition de ressources humaines et matérielles aura un coût. La formation du personnel aura un coût. Mais ce coût sera amoindri par le fait que, une fois cette migration réalisée, les prochaines migrations n’auront un coût qu’au niveau matériel principalement.

La qualité de la première migration est cruciale. Si le but est de migrer les autres bases de données de l’entreprise, il est essentiel que la première migration soit une réussite totale. Il est essentiel qu’elle soit documentée, discutée, pour que le travail effectué soit réutilisable (soit complètement, soit uniquement l’expérience et les méthodes) afin que la prochaine migration soit moins coûteuse.

Pour résumer, la première migration doit être suffisamment simple pour ne pas être un échec et suffisamment complexe pour être en confiance pour les prochaines migrations. Il est donc essentiel de bien choisir la base de sa première migration.

Il est aussi essentiel d’avoir des ressources humaines et matérielles suffisantes, tout en contrôlant les coûts.

Enfin, il est important de ne pas stresser les acteurs de cette migration avec des délais difficiles à tenir. Le service est déjà présent et fonctionnel, la première migration doit être un succès si l’on veut continuer, autant donner du temps aux équipes responsables de la migration.

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. Par exemple, les tablespaces sont disponibles dans les deux SGBD, mais l’implémentation est différente. Pour Oracle, il s’agit d’un fichier qu’il faut dimensionner. Pour PostgreSQL, il s’agit d’un répertoire dont la taille est libre. Il y a des avantages et des inconvénients à chaque implémentation. Au niveau d’Oracle, il suffit de surveiller l’espace libre au niveau du tablespace. Avec PostgreSQL, la supervision se fait au niveau du système d’exploitation. C’est plutôt un défaut de PostgreSQL, car, de nos jours, les équipes système et base de données sont séparées. Par contre, le problème au niveau Oracle vient du dimensionnement des tablespaces. Il faut surveiller plus fortement au niveau Oracle, car il est nécessaire d’agrandir le tablespace si jamais ce dernier vient à être utilisé complètement. Il n’y a pas ce problème avec PostgreSQL, tout du moins tant qu’il reste de l’espace disque.

Cette partie est donc consacrée à la revue des pièges à éviter et à montrer les différences d’implémentation pouvant susciter des problèmes ou des incompréhensions lors d’une migration.

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, certains moteurs se permettent des écarts par rapport à la norme, parfois pour gagner en performances, mais surtout pour faciliter la vie des développeurs. Cela fait que beaucoup de développeurs utilisent ces écarts à la norme, parfois 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 à 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.

Ils partagent aussi certains concepts, comme les transactions et les points de retournements (savepoint), MVCC et la gestion des verrous. Cela permet de conserver les logiques applicative et algorithmique, au moins jusqu’à une certaine mesure.

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;
WARNING:  there is no transaction in progress
ROLLBACK
dev2=# SELECT * FROM t1;
 id
----
  1
(1 row)

Par contre, si j’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)

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 (END, 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)

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.

Oracle ne dispose pas du type booléen. Du coup, les développeurs utilisent fréquemment un entier qu’ils mettront à 0 pour FALSE et à 1 pour TRUE. 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, les ORM chercheront un booléen parce que le code applicatif indique un booléen. Cela provoquera une erreur sur PostgreSQL, comme le montre l’exemple suivant :

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 t3 (c1 boolean);
CREATE TABLE
dev2=# INSERT INTO t3 VALUES (true);
INSERT 0 1
dev2=# INSERT INTO t3 VALUES ('f');
INSERT 0 1
dev2=# SELECT * FROM t3;
 c1
----
 t
 f
(2 rows)

Au niveau de PostgreSQL, il existe trois types de données pour les chaînes de caractères : char, varchar et 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 est de 1 Go. Suivant l’encodage, le nombre de caractères intégrables dans la colonne diffère.

La 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 colonne par colonne et peut être modifié au sein d’une requête (au niveau d’un ORDER BY ou d’un CREATE INDEX).

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 parlé 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 depuis la 9.3 (2 Go avant).

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.

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.

L’un des gros avantages de PostgreSQL est son extensibilité. 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.

Oracle a tendance à mélanger un peu tous les types dates. Ce n’est pas le cas au niveau de PostgreSQL. Une colonne de type date au niveau de PostgreSQL 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 les types timestamp (date et heure). Le stockage est fait en UTC, mais la restitution dépend du fuseau horaire indiqué par le client.

Oracle contient de nombreuses extensions (lisez incompatibilité) avec la norme SQL.

Une ancienne écriture des jointures utilisait le signe +. Ces requêtes doivent être réécrites en utilisant la notation LEFT JOIN du standard SQL.

Oracle utilise le CONNECT BY pour permettre l’écriture de requêtes récursives. PostgreSQL supporte les requêtes récursives depuis la 8.3, mais avec la syntaxe de la norme SQL, à savoir WITH RECURSIVE. Cette syntaxe est décrite dans la documentation de PostgreSQL.

De nombreuses fonctions ont des noms différents entre Oracle et PostgreSQL. Par exemple, la fonction NVL sous Oracle s’appelle COALESCE sous PostgreSQL.

La casse par défaut des objets est différente entre Oracle et PostgreSQL. C’est d’ailleurs un exemple où Oracle respecte mieux le standard SQL que PostgreSQL. Si la casse n’est pas spécifiée, le nom de l’objet sera en majuscule sous Oracle et en minuscule sous PostgreSQL. Pour forcer la casse, il faudra utiliser des guillemets doubles, comme le montre cet 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)

Une simple lecture du code source est facilement source d’erreurs. Bien qu’il soit toujours intéressant de faire une première passe pour corriger les cas les plus flagrants, il est nécessaire ensuite de tester l’application 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();

Les langages de routines stockées sont différents entre Oracle et PostgreSQL. Même si PL/pgSQL est un langage assez proche de PL/SQL, cela demandera une revue des routines stockées et une réécriture (automatique ou manuelle) des routines.

PostgreSQL dispose de plusieurs langages de routines stockées. Le langage PL/pgSQL est très proche du langage PL/SQL. Cela n’empêche qu’un travail d’adaptation sera nécessaire. Certaines fonctionnalités ou objets manquent : PostgreSQL ne dispose pas de packages, il ne dispose pas des transactions autonomes, il ne dispose pas de certaines fonctionnalités comme les directories (PL/pgSQL est un langage dit sûr dans le sens où il n’a accès qu’à la base de données via le langage SQL).

Il existe évidemment des contournements à ces différents manques (respectivement les schémas, dblink ou pg_background, et les langages du types PL/Perl ou PL/Python). Cependant, cela demande un travail d’adaptation plus important qu’il faut prendre en compte dans le cadre d’une migration. C’est en fait là que se situe le plus gros du travail de portage.

Pour aller plus loin :

• Support des transactions autonomes dans PostgreSQL

Avant de pouvoir traiter le code, qu’il soit applicatif ou issu des routines stockées, il faut procéder à la migration du schéma et des données. C’est donc ce dont nous allons parler dans cette partie.

La première question à se poser concerne le schéma : veut-on le migrer tel quel ? Le changer peut permettre d’utiliser des fonctionnalités plus avancées de PostgreSQL. Cela peut être intéressant pour des raisons de performances, mais a comme inconvénient de ne plus être une migration isofonctionnelle.

Généralement, il faudra créer un nouveau schéma, et intégrer les objets par étapes : tables, index, puis contraintes.

Oracle utilise généralement number pour les types entiers. L’équivalent strict au niveau PostgreSQL est numeric mais il est préférable de passer à d’autres types de données comme int (un entier sur quatre octets) ou bigint (un entier sur huit octets) qui sont bien plus performants.

L’outil pour la migration devra être sélectionné suivant ses possibilités au niveau de la transformation de certains types en d’autres types, si jamais il est décidé de procéder ainsi.

L’outil de migration doit pouvoir aussi gérer des types particuliers, comme les types spécifiques à la recherche plein texte, ceux spécifiques aux objets binaires, ceux spécifiques à la couche spatiale, etc. Il est possible qu’un développement soit nécessaire pour faciliter la migration. Un outil libre est préférable dans ce cas.

Pour des raisons de performances, il est toujours préférable de ne déclarer les index et les contraintes qu’une fois les tables remplies. L’outil de migration doit aussi prendre cela en compte : création des tables, remplissage des tables et enfin création des index et contraintes.

Toujours dans les décisions à prendre avant la migration, il est important de savoir si l’on veut tout migrer d’un coup ou le faire en plusieurs étapes. Les deux possibilités ont leurs avantages et inconvénients.

De même, souhaite-t-on paralléliser l’import et l’export ? De ce choix dépend principalement l’outil que l’on va sélectionner pour la migration.

Enfin, souhaite-t-on modifier des données lors de l’opération de migration ? Là aussi, cela peut se concevoir, notamment si certains types de données sont modifiés. Mais c’est une décision à prendre lors des premières étapes du projet.

Après avoir répondu aux questions précédentes et évalué la complexité de la migration, il sera possible de sélectionner le bon outil de migration. Il en existe différents, qui répondront différemment aux besoins.

Ora2Pg est un outil libre développé par Gilles Darold. Le rythme de développement est rapide. De nouvelles fonctionnalités sont proposées rapidement, suivant les demandes des utilisateurs, les nouveautés dans PostgreSQL et les découvertes réalisées par son auteur.

Les ETL sont intéressants pour les possibilités plus importantes. Ora2Pg ne fait que de la conversion Oracle vers PostgreSQL et MySQL, alors que les ETL autorisent un plus grand nombre de sources de données et de destinations, si bien que l’expérience acquise pour la migration d’Oracle vers PostgreSQL peut être réutilisée pour réaliser la migration d’un autre moteur vers un autre moteur ou pour l’import ou l’export de données.

Il est aussi possible de développer sa propre solution si les besoins sont vraiment spécifiques au métier, voire de mixer différentes solutions. Par exemple, il était intéressant d’utiliser Ora2Pg pour la transformation du schéma et un ETL pour un export et import des données parallélisés (ce n’est plus le cas maintenant qu’Ora2Pg est lui aussi parallélisé).

Ora2Pg est un outil écrit en Perl. Il se connecte à Oracle via le connecteur Perl pour Oracle. Après analyse des catalogues système Oracle et lecture de son fichier de configuration, il est capable de générer un fichier SQL compatible avec PostgreSQL ou de se connecter à une base PostgreSQL pour y exécuter ce script. Dans les dernières versions, il est même capable de convertir automatiquement une partie du code PL/SQL d’Oracle vers du PL/pgSQL sur PostgreSQL.

L’outil est plutôt simple de mise en œuvre et de prise en main. Il est rapide au chargement, notamment grâce à sa gestion de la commande COPY.

Pour aborder immédiatement les inconvénients de Ora2Pg, il ne propose pas de solution incrémentale : c’est tout ou partie d’une table ou rien.

Ora2Pg dispose néanmoins de nombreuses fonctionnalités. Il est capable d’exporter tout le schéma de données Oracle. Il est capable de convertir utilisateurs et droits sur les objets. Il convertit aussi automatiquement la conversion des types de données. Enfin, il s’occupe de la déclaration et du code des routines stockées (uniquement PL/SQL vers PL/pgSQL). Il propose aussi une aide au partitionnement, dont l’implémentation est vraiment différente entre Oracle et PostgreSQL.

Les ETL sont spécialisées dans la transformation et le chargement des données. Ils permettent la parallélisation pour leur traitement, ils sont très souples au niveau de la transformation de données. Tout cela leur permet d’être très rapide, quelques fois plus qu’Ora2Pg.

De plus, ils permettent de faire de la migration incrémentale.

La migration du schéma est au mieux sommaire, voire inexistante. Ce n’est clairement pas la fonctionnalité visée par les ETL.

Le paramétrage d’un ETL est souvent très long. Si vous devez migrer les données de 200 tables, vous aurez 200 jobs à créer. Dans ce cas, Ora2Pg est bien plus intéressant, vu que la migration de la totalité des tables est l’option par défaut.

Ce sont des outils riches et donc complexes. Cela demandera un apprentissage bien plus long que pour Ora2Pg. Cependant, ils sont utilisables dans bien plus de cas que Ora2Pg.

Certaines fonctionnalités Oracle n’ont pas d’équivalents natifs dans PostgreSQL. Nous allons étudier les deux cas les plus fréquents : les vues matérialisées et le partitionnement.

Le but d’une vue matérialisée est de stocker physiquement le résultat de l’exécution d’une vue et d’utiliser par la suite ce stockage plutôt que le résultat de l’exécution de la requête. Il est possible de créer des index sur cette vue matérialisée. Elle est mise à jour soit à la demande soit au fil de l’eau.

Les vues matérialisées ne sont supportées qu’à partir de la version 9.3 pour PostgreSQL. Elles ne supportent pas toutes les fonctionnalités qu’offre Oracle : pas de mise à jour au fil de l’eau, pas de rafraîchissement incrémental à l’aide de journaux de vue matérialisée (MATERIALIZED VIEW LOG sous Oracle), pas de réécriture de requête.

PostgreSQL implémente les vues matérialisées, bien que certaines fonctionnalités que propose Oracle (ex : FAST REFRESH à l’aide des MATERIALIZED VIEW LOG) ne soient pas encore présentes dans les versions actuelles de PostgreSQL.

dev2=# CREATE TABLE t1 (id integer);
CREATE TABLE
dev2=# INSERT INTO t1 VALUES (1), (10);
INSERT 0 2
dev2=# CREATE MATERIALIZED VIEW v1 AS SELECT * FROM t1 WHERE id<10;
SELECT 1
dev2=# SELECT * FROM v1;
 id
----
  1
(1 row)

L’actualisation de la vue v1 nécessite l’exécution manuelle ou planifiée de l’instruction REFRESH MATERIALIZED VIEW.

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

dev2=# REFRESH MATERIALIZED VIEW v1;
REFRESH MATERIALIZED VIEW
dev2=# SELECT * FROM v1;
 id 
----
  1
  2
(2 rows)

Il est possible de lever le verrou exclusif de l’opération de rafraîchissement à l’aide de l’option CONCURRENTLY. Pour ce faire, un index unique est requis sur l’une des colonnes de la vue matérialisée qui respecte la contrainte d’unicité.

dev2=# INSERT INTO t1 VALUES (3);
dev2=# REFRESH MATERIALIZED VIEW CONCURRENTLY v1;
ERROR:  cannot refresh materialized view "public.v1" concurrently
HINT:  Create a unique index with no WHERE clause on one or 
       more columns of the materialized view.
dev2=# CREATE UNIQUE INDEX ON v1(id);
CREATE INDEX
dev2=# REFRESH MATERIALIZED VIEW CONCURRENTLY v1;
REFRESH MATERIALIZED VIEW
dev2=# SELECT * FROM v1;
 id 
----
  1
  2
  3
(3 rows)

Si une mise à jour au fil de l’eau est requise, il faudra forcément passer par des triggers.

dev2=# CREATE FUNCTION fct_refresh_v1() RETURNS trigger LANGUAGE plpgsql
AS $$
  BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY v1;
    RETURN new;
END
$$;
CREATE FUNCTION
dev2=# CREATE TRIGGER trg_t1_on_insert AFTER INSERT ON t1 FOR EACH ROW
        EXECUTE PROCEDURE fct_refresh_v1();
CREATE TRIGGER
dev2=# INSERT INTO t1 VALUES (4);
INSERT 0 1
dev2=# SELECT * FROM v1;
 id 
----
  1
  2
  3
  4
(4 rows)

Pour aller plus loin :

• Postgres 9.4 feature highlight - REFRESH CONCURRENTLY a materialized view
• Sur les mécanismes internes : Don’t forget materialized view (Vidéo)

Oracle dispose de clauses dans la commande CREATE TABLE permettant de définir simplement les partitions et la méthode de partitionnement.

Depuis PostgreSQL v10, il est possible de déclarer une table comme étant partitionnée et de déclarer des partitions. La spécification d’une table partitionnée consiste en une méthode de partitionnement et une liste de colonnes ou expressions à utiliser comme la clé de partitionnement.

Toutes les lignes insérées dans la table partitionnée seront alors redirigées vers une des partitions en se basant sur la valeur de la clé de partitionnement.

Pour l’instant, les méthodes de partitionnement supportées sont le partitionnement par intervalles (RANGE), par liste (LIST) et par hachage (HASH), ce dernier depuis la version 11.

Les partitions peuvent elles-mêmes être définies comme des tables partitionnées, en utilisant le sous-partitionnement. Les partitions peuvent avoir leurs propres index, contraintes et valeurs par défaut, différents de ceux des autres partitions. À partir de la version 11, les index peuvent être créés au niveau de la table partitionnée afin de propager la définition et la construction d’index aux partitions. À partir de la version 12, les clés étrangères peuvent être créées sur les tables partitionnées.

Avant la version 10, PostgreSQL ne dispose pas de ces clauses. Il est nécessaire de passer par l’héritage pour la création des partitions, par l’ajout de contraintes CHECK pour que le planificateur sache qu’il peut ne parcourir que certaines partitions, et par l’ajout de triggers pour que les insertions, mises à jour et suppressions se passent correctement.

Autrement dit, la mise en place du partitionnement sous PostgreSQL avant la version 10 est lourde et peu intuitive.

Pour aller plus loin :

• Benchmarks sur le partitionnement sous PostgreSQL

En plus d’une mise en place difficile et d’une administration tout autant complexe, un bon nombre d’autres inconvénients sont présents.

L’unicité globale ne peut pas être assurée. PostgreSQL dépend d’un index pour assurer ou forcer cette contrainte, et il n’est pas possible de créer un index sur plusieurs tables. Du coup, une table partitionnée ne peut pas avoir de contrainte unique ou de clé primaire. De ce fait, il n’est pas non plus possible d’utiliser des clés étrangères vers cette table.

L’installation est détaillée ici pour Red Hat/CentOS 7 et 8, et Debian/Ubuntu.

Elle ne dure que quelques minutes.

Sur Red Hat 7 / Cent OS 7

Installation du dépôt communautaire  :

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent peuvent être générées par l’assistant sur : https://www.postgresql.org/download/linux/redhat/, en précisant :

• la version majeure de PostgreSQL (ici la 13) ;
• la distribution (ici CentOS 7) ;
• l’architecture (ici x86_64, la plus courante).

# yum install https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Installation de PostgreSQL 13 : 

# yum install postgresql13-server

Tout à fait optionnellement, une fonctionnalité avancée, le JIT (Just In Time compilation), nécessite un paquet séparé, qui lui-même nécessite des paquets du dépôt EPEL :

# yum install epel-release
# yum install postgresql13-llvmjit

Création d’une première instance :

Il est conseillé de déclarer PG_SETUP_INITDB_OPTIONS, notamment pour mettre en place les checksums et forcer les traces en anglais :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-13/bin/postgresql-13-setup initdb
# cat /var/lib/pgsql/13/initdb.log

Ce dernier fichier permet de vérifier que tout s’est bien passé.

Chemins :

Configuration :

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

# systemctl start postgresql-13
# systemctl stop postgresql-13
# systemctl reload postgresql-13

Test rapide de bon fonctionnement

# systemctl --all |grep postgres
# sudo -iu postgres psql

Démarrage de l’instance au démarrage du système d’exploitation :

# systemctl enable postgresql-13

Consultation de l’état de l’instance :

# systemctl status postgresql-13

Ouverture du firewall pour le port 5432 :

Si le firewall est actif (dans le doute, consulter systemctl status firewalld) :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

Création d’autres instances :

Si des instances de versions majeures différentes doivent être installées, il faudra installer les binaires pour chacune, et l’instance par défaut de chaque version vivra dans un sous-répertoire différent de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf.

Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faudra les installer dans des PGDATA différents.

• Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd).

• Respecter les normes et conventions de l’OS : placer les instances dans un sous-répertoire de /var/lib/pgsqsl/13/ (ou l’équivalent pour d’autres versions majeures).

• Création du fichier service de la deuxième instance :

# cp /lib/systemd/system/postgresql-13.service \
                          /etc/systemd/system/postgresql-13-secondaire.service

• Modification du fichier avec le nouveau chemin :

Environment=PGDATA=/var/lib/pgsql/13/secondaire

• Option 1 : création d’une nouvelle instance vierge :

# /usr/pgsql-13/bin/postgresql-13-setup initdb postgresql-13-secondaire

• Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.

• Adaptation de postgresql.conf (port !), recovery.conf…

• Commandes de maintenance :

# systemctl [start|stop|reload|status] postgresql-13-secondaire
# systemctl [enable|disable] postgresql-13-secondaire

• Ouvrir un port dans le firewall au besoin.

Sur Red Hat 8 / Cent OS 8

Fondamentalement, le principe reste le même qu’en version 7, mais il faudra utiliser dnf plutôt que yum, désactiver le module PostgreSQL par défaut, et il est inutile d’installer un dépôt EPEL :

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf install -y postgresql13-server postgresql13-llvmjit

La création de l’instance et la suite sont identiques à Red Hat/CentOS 7.

Sur Debian / Ubuntu

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Installation du dépôt communautaire :

Référence : https://apt.postgresql.org/ 

• Import des certificats et de la clé :

# apt install curl ca-certificates gnupg
# curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

• Création du fichier du dépôt /etc/apt/sources.list.d/pgdg.list (ici pour Debian 10 « buster » ; adapter au nom de code de la version de Debian ou Ubuntu correspondante : stretch, bionic, focal…) :

deb https://apt.postgresql.org/pub/repos/apt/ buster-pgdg main

Installation de PostgreSQL 13 :

# apt update
# apt install postgresql-13 postgresql-client-13

(Pour les versions 9.x, installer aussi le paquet postgresql-contrib-9.x).

La première instance est directement créée, démarrée et déclarée comme service à lancer au démarrage du système.

Chemins :

Configuration

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

Debian fournit ses propres outils :

# pg_ctlcluster 13 main [start|stop|reload|status]

Démarrage de l’instance au lancement :

C’est en place par défaut, et modifiable dans /etc/postgresql/13/main/start.conf.

Ouverture du firewall :

Debian et Ubuntu n’installent pas de firewall par défaut.

Statut des instances :

# pg_lsclusters

Test rapide de bon fonctionnement

# systemctl --all |grep postgres
# sudo -iu postgres psql

Destruction d’une instance :

# pg_dropcluster 13 main

Création d’autres instances :

Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :

• les paramètres de création d’instance dans /etc/postgresql-common/createcluster.conf peuvent être modifiés, par exemple ici pour : les checksums, les messages en anglais, l’authentification sécurisée, le format des traces et un emplacement séparé pour les journaux :

initdb_options = '--data-checksums --lc-messages=C --auth-host=scram-sha-256 --auth-local=peer'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'

• création de l’instance, avec possibilité là aussi de préciser certains paramètres du postgresql.conf voire de modifier les chemins des fichiers (déconseillé si vous pouvez l’éviter) :

# pg_createcluster 13 secondaire \
  --port=5433 \
  --datadir=/PGDATA/11/basedecisionnelle \
  --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \
  --  --data-checksums --waldir=/ssd/postgresql/11/basedecisionnelle/journaux

• démarrage :

# pg_ctlcluster 11 secondaire start

Accès à l’instance

Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire :

$ sudo -iu postgres psql
psql (13.0)
Saisissez « help » pour l'aide.
postgres=#

Ce qui suit permet la connexion directement depuis un utilisateur du système :

Pour des tests (pas en production !), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf :

local   all             postgres                               trust

La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée :

dalibo:~$ psql -U postgres
psql (13.0)
Saisissez « help » pour l'aide.
postgres=#

Une authentification par mot de passe est plus sécurisée :

• dans pg_hba.conf, mise en place d’une authentification par mot de passe (md5 par défaut) pour les accès à localhost :

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

(une authentification scram-sha-256 est plus conseillée mais elle impose que password_encryption soit à cette valeur dans postgresql.conf avant de définir les mots de passe).

• ajout d’un mot de passe à l’utilisateur postgres de l’instance ;

dalibo:~$ sudo -iu postgres psql
psql (13.0)
Saisissez « help » pour l'aide.
postgres=# \password
Saisissez le nouveau mot de passe :
Saisissez-le à nouveau :
postgres=# \q

dalibo:~$ psql -h localhost -U postgres
Mot de passe pour l'utilisateur postgres :
psql (13.0)
Saisissez « help » pour l'aide.
postgres=#

• pour se connecter sans taper le mot de passe, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion :

localhost:5432:*:postgres:motdepassetrèslong

• ce fichier doit être protégé des autres utilisateurs :

$ chmod 600 ~/.pgpass

• pour n’avoir à taper que psql, on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc :

export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost

Rappels :

• en cas de problème, consulter les traces (dans /var/lib/pgsql/13/data/log ou /var/log/postgresql/) ;
• toute modification de pg_hba.conf implique de recharger la configuration par une de ces trois méthodes selon le système :

root:~# systemctl reload postgresql-13

root:~# pg_ctlcluster 13 main reload

postgres:~$ psql -c 'SELECT pg_reload_conf();'

TP1.1

Correspondance des types de données

Donner les correspondances pour PostgreSQL des types de données Oracle suivants :

• NUMBER(4)
• NUMBER(10)
• NUMBER(9,3)
• NUMBER
• VARCHAR(25)
• VARCHAR2
• BLOB
• CLOB

Aidez-vous de la documentation PostgreSQL sur les types de données : http://docs.postgresql.fr/current/datatype.html

TP1.2

Champs NULL

La requête SELECT suivante ne retourne pas le même résultat selon qu’elle est exécutée sur Oracle ou sur PostgreSQL (2 lignes sous Oracle et 1 sous PostgreSQL), pourquoi ?

CREATE TABLE label (id NUMBER, lbl VARCHAR(25));
INSERT INTO label VALUES (1, 'Label 1');
INSERT INTO label VALUES (2, NULL);
INSERT INTO label VALUES (3, 'Label 3');
INSERT INTO label VALUES (4, '');

SELECT count(id) FROM label WHERE lbl IS NULL;

Réécrire la requête SELECT pour qu’elle renvoie le même résultat sur PostgreSQL que sur Oracle.

TP1.3

Concaténation de chaine NULL

Réécrire la requête suivante pour obtenir le même résultat sur PostgreSQL :

SELECT 'Label'||NULL FROM DUAL;

LABEL
-----
Label

Note : Exécutez la requête sur PostgreSQL pour voir la valeur retournée.

TP1.4

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

TP1.5

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 * FROM employees e, departments d
 WHERE e.employee_id = d.manager_id (+)

Puis ce code :

SELECT * FROM appellation a, region r
 WHERE a.region_id (+) = r.id;

TP1.6

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;

TP1.7

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)

TP1.8

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;

Conseil : les fonctions sur les chaînes de caractères sont listées ici : http://docs.postgresql.fr/current/functions-string.html

TP1.9

CONNECT BY

Réécrire la requête suivante à base de CONNECT BY sous Oracle :

SELECT numero, nom, fonction, manager
  FROM employes
  START WITH manager IS NULL
  CONNECT BY PRIOR numero = manager;

Cette requête explore la hiérarchie de la table EMPLOYES. La colonne manager 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 IS NULL de la requête. Le lien avec l’employé et son responsable hiérarchique est construit avec la clause CONNECT BY PRIOR numero = manager qui indique que la valeur de la colonne manager correspond à l’identifiant numero du niveau de hiérarchie précédent.

Voici le retour de cette requête sous Oracle :

1 | A. Boss       | DIRECTEUR      |
4 | C. Second     | SOUS DIRECTEUR | 1
5 | F. Dsi        | DSI            | 1
3 | C. Secretaire | ASSISTANTE     | 1
2 | J.P Devel     | DEVELOPPEUR    | 5

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’information voir la documentation : https://docs.postgresql.fr/current/queries-with.html

Voici les ordres SQL permettant de créer cette table et d’y insérer quelques données pour faciliter les tests de réécriture.

CREATE TABLE employes (
  numero integer,
  nom text,
  fonction text,
  manager integer
);

INSERT INTO employes VALUES (1, 'A. Boss', 'DIRECTEUR', NULL);
INSERT INTO employes VALUES (4, 'C. Second', 'SOUS DIRECTEUR', 1);
INSERT INTO employes VALUES (5, 'F. Dsi', 'DSI', 1);
INSERT INTO employes VALUES (2, 'J.P Devel', 'DEVELOPPEUR', 5);
INSERT INTO employes VALUES (3, 'C. Secretaire', 'ASSISTANTE', 1);

TP1.1

Correspondance des types de données

• À NUMBER(4) correspond SMALLINT
• À NUMBER(10) correspond BIGINT
• À NUMBER(9,3) correspond NUMERIC(9,3)
• À NUMBER correspond NUMERIC
• VARCHAR(25) est identique sur les deux SGBDR
• VARCHAR2 peut être traduit en VARCHAR() ou TEXT
• À BLOB correspond le type BYTEA
• et CLOB est un champ TEXT sous PostgreSQL.

Ici il y a une particularité avec le type NUMBER sans paramètres qui peut aussi bien être un entier qu’un décimal. Il convient dans ce cas de vérifier les données pour utiliser le type adéquat. S’il s’agit de données de type monétaire, comme des tarifs ou montants de facture, ou tout autre donnée pour laquelle une grande précision est demandée, il est impératif d’utiliser le type numérique pour éviter les effets de bord des arrondis.

TP1.2

Champs NULL

La requête ne retourne pas le même résultat selon si elle est exécutée sur Oracle ou sur PostgreSQL car sous Oracle, le type de données VARCHAR ou VARCHAR2 assimile la chaîne vide à la valeur NULL. Ce comportement fait que les id 2 et 4 sont retournés par Oracle, alors que PostgreSQl ne retournera que l’id 2.

L’instruction Oracle de création de la table

CREATE TABLE label (id NUMBER, lbl VARCHAR(25));

Devient :

CREATE TABLE label (id INTEGER, lbl VARCHAR(25));

Pour émuler le comportement non standard d’Oracle, voici la requête qui peut être utilisée à cet effet sur PostgreSQL :

-- SELECT count(id) FROM label WHERE lbl IS NULL;
-- devient
SELECT count(id) FROM label WHERE lbl IS NULL OR (lbl = '');

TP1.3

Concaténation de champ NULL

De même que dans l’exercice précédent, Oracle permet de concaténer une chaîne avec une valeur NULL sans problèmes. Avec PostgreSQL, la valeur NULL est propagée dans les opérations : une valeur NULL concaténée à une chaîne de caractère donne NULL.

Pour émuler le fonctionnement d’Oracle, il faudra réécrire la requête comme suit :

-- SELECT 'Label'||NULL FROM DUAL;
-- devient
SELECT coalesce('Label'||NULL, 'Label');

TP1.4

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;

TP1.5

Jointures (+)

La première requête correspond à une jointure de type LEFT OUTER JOIN :

-- SELECT * FROM employees e, departments d
--  WHERE e.employee_id = d.manager_id (+)
-- devient
SELECT *
  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 * FROM appellation a, region r
-- WHERE a.region_id (+) = r.id;
-- devient
SELECT * 
  FROM appellation a
 RIGHT OUTER JOIN region r ON a.region_id = r.id;

TP1.6

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

TP1.7

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