Dalibo SCOP
Formation | Module N3 |
Titre | RequĂȘtes SQL |
RĂ©vision | 24.12 |
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.
Cette formation est sous licence CC-BY-NC-SA. Vous ĂȘtes libre de la redistribuer et/ou modifier aux conditions suivantes :
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.
PostgreSQLŸ PostgresŸ et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.
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.
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.
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.
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.
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 (
integer,
did varchar(40),
name 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,
VARCHAR2(10),
first_name NUMBER(9,2),
salary NUMBER(3),
commission NUMBER GENERATED ALWAYS AS
salary2 ROUND(salary*(1+commission/100),2)) VIRTUAL
( );
Et voici la version Ă base dâune vue dans PostgreSQLÂ :
CREATE TABLE employees (
id bigint,
varchar(10),
first_name double precision,
salary integer
commission
);
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,
varchar(10),
first_name double precision,
salary integer,
commission double precision generated always as
salary2 ROUND((salary*(1+commission/100))::numeric,2)) STORED
( );
En savoir plus : Colonnes générées
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 :
=# CREATE TABLE toto(id integer);
dev2CREATE TABLE
=# CREATE TABLE TitI(id integer);
dev2CREATE TABLE
=# \d
dev2List of relations
Schema | Name | Type | Owner
--------+------+-------+-----------
public | t1 | table | guillaume
public | t3 | table | guillaume
public | titi | table | guillaume
public | toto | table | guillaume
4 rows)
(
=# CREATE TABLE "TitI"(id integer);
dev2CREATE TABLE
=# \d
dev2List 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) (
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 (
CHAR(3),
a_code CHAR(8)
p_number );
et la version PostgreSQLÂ :
CREATE TYPE phone_t AS (
char(3),
a_code char(8)
p_number );
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.
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.
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.
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.
=# SELECT cast('' AS varchar) IS NULL;
dev2column?
?----------
f1 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
).
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 :
=# CREATE TABLE t1 (c1 int);
dev2CREATE TABLE
=# INSERT INTO t1 VALUES (true);
dev2column "id" is of type integer but expression is of type boolean
ERROR: 1: insert into t1 values (true);
LINE
^to rewrite or cast the expression.
HINT: You will need =# INSERT INTO t1 VALUES ('t');
dev2for integer: "t"
ERROR: invalid input syntax 1: insert into t1 values ('t');
LINE
^=# CREATE TABLE t2 (c1 boolean);
dev2CREATE TABLE
=# INSERT INTO t2 VALUES (true);
dev2INSERT 0 1
=# INSERT INTO t2 VALUES ('f');
dev2INSERT 0 1
=# SELECT * FROM t2;
dev2
c1---
t
f2 rows) (
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.
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 :
=# SELECT t + interval '1h' FROM moments ;
postgrescolumn?
?----------------------------------
2021-05-10 23:26:21.914092+05:45
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,
AS localtime,
pg_typeof(localtime) current_timestamp) AS current_timestamp,
pg_typeof(AS current_time,
pg_typeof(current_time) current_date) AS current_date \gx pg_typeof(
-[ 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
).
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.
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.
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.
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;
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;
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.
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.
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;
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.
=# EXPLAIN SELECT * FROM t1 ORDER BY col DESC LIMIT 10; test
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;
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;
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;
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;
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;
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.
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.
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;
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 ||
:text AS path
emp.ename: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
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 ||
:text AS path, emp.ename = ANY(prior.path) AS cycle
emp.ename: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;
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 :
is a WITH item named "employees", but it cannot be referenced
DĂTAIL : There from this part of the query.
Use WITH RECURSIVE, or re-order the WITH items to remove forward
ASTUCE : 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Ă©.
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)
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.
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;
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.
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();
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/n3_solutions.
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,
"Proposed Salary"
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
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
FIRST 20 ROWS ONLY; FETCH
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,
"Proposed Salary"
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
.