Le guide complet de la correspondance des types de données Oracle vers PostgreSQL

TL;DR : La plupart des correspondances de types de données Oracle vers PostgreSQL sont simples.
Les dangereux ne le sont pas.
La date Oracle inclut une composante temporelle que la date PostgreSQL élimine silencieusement.
Oracle NUMBER est mappé à NUMERIC par défaut, ce qui est correct mais lent pour les charges de travail entières.
Les chaînes vides dans Oracle sont NULL ; dans PostgreSQL, elles ne le sont pas.
Ce guide vous donne la table de correspondance complète et couvre les cinq pièges qui entraînent une corruption silencieuse des données lors de la migration.

Le problème de données le plus courant dans un exercice de mappage de types de données d'Oracle à PostgreSQL n'est pas une colonne manquante ou un échec d'importation.

C'est une correspondance qui semble correcte, passe la validation et modifie vos données en silence.

Les projets de migration où les dates ont perdu leur composante temporelle, où le calcul entier est devenu extrêmement lent et où la logique applicative a été compromise parce que les chaînes vides ont cessé de se comporter comme NULL — aucun de ces problèmes n'était évident au stade de la conversion du schéma.

Elles étaient toutes évitables.

Ce guide vous donne la table de correspondance complète et explique les décisions qui comptent.

Si vous êtes au début de votre Migration d'Oracle vers PostgreSQL, enregistrez cette page.

Tu y reviendras.

Table des matières

Tableau complet de mappage des types de données Oracle vers PostgreSQL

Le tableau ci-dessous couvre tous les types Oracle courants.

La colonne “ Safe Default ” est ce qu'ora2pg utilise automatiquement.

La colonne “ Recommandé ” est celle que vous devriez réellement utiliser après avoir analysé vos données.

Type Oracle	Sûr par défaut	Recommandé	Notes
NOMBRE	NUMÉRIQUE	ENTIER, GRAND ENTIER, ou NUMÉRIQUE	Dépend de l'échelle et de la portée — voir la section ci-dessous
NUMBER(p, 0)	NUMÉRIC(p)	SMALLINT / INTEGER / BIGINT	Utilisez des types entiers pour de meilleures performances
NOMBRE(p, s)	NUMERIC(p, s)	NUMERIC(p, s)	Correspondance exacte
VARCHAR2(n)	VARCHAR(n)	VARCHAR(n) ou TEXTE	n est le nombre de caractères dans PG ; peut nécessiter un ajustement pour les colonnes sémantiques en octets
NVARCHAR2(n)	VARCHAR(n)	VARCHAR(n)	PG est toujours UTF-8 ; aucun type unicode séparé n'est nécessaire
CAR(n)	CAR(n)	CAR(n)	Correspondance directe
DATE	HORODATEUR	HORODATEUR	Oracle DATE inclut l'heure — ne jamais mapper sur PG DATE
HORODATEUR	HORODATEUR	HORODATEUR	Correspondance directe
HORODATE AVEC DÉCALAGE HORAIRE	TIMESTAMPTZ	TIMESTAMPTZ	Les sémantiques de stockage diffèrent — voir la section ci-dessous
HORODATAGE AVEC FUSEAU HORAIRE LOCAL	TIMESTAMPTZ	TIMESTAMPTZ	Même mise en garde
INTERVALLE ANCIENNE À MOIS	INTERVALLE	INTERVALLE	BG INTERVAL est un type flexible unique
INTERVALLE JOUR AU SECONDE	INTERVALLE	INTERVALLE	Tester l'arithmétique des dates aux limites mois/jour
CLOB	Texte	Texte	PG TEXT illimité en pratique (jusqu'à ~1 Go via TOAST)
NCLOB	Texte	Texte	Idem
BLOB	BYTEA	BYTEA ou LOB	lo pour les gros fichiers ou les cas d'utilisation de streaming
BRUT(n)	BYTEA	BYTEA	Remplacement direct
LONG BRUT	BYTEA	BYTEA	Obsolète dans Oracle ; migrez rapidement
LONGUE	Texte	Texte	Obsolète dans Oracle
XMLTYPE	XML	XML	La requête PG XML utilise XPATH() et XMLTABLE()
FLOTTANT_BINAIRE	RÉEL	RÉEL	Nombre flottant IEEE 754 4 octets – correspondance directe
BINAIRE_DOUBLE	DOUBLE PRÉCISION	DOUBLE PRÉCISION	8-octet IEEE 754 — correspondance directe
BOOLEAN (Oracle 23c+)	BOULÉEN	BOULÉEN	Pre-23c : NUMBER(1) ou CHAR(1) — conversion manuelle requise
ROWID / UROWID	Aucun équivalent	Refonte avec clé primaire	Le ctid de PG n'est pas stable après un VACUUM ou un clustering.
SDO_GEOMETRY	Pas intégré	PostGIS GEOMETRY	Nécessite l'extension PostGIS

Pourquoi le type de données DATE d'Oracle pose-t-il problème dans PostgreSQL ?

Oracle DATE stocke la date et l'heure. PostgreSQL DATE stocke uniquement la date.
Le mappage d'Oracle DATE vers PostgreSQL DATE ignore silencieusement le composant temporel de chaque valeur de cette colonne — aucune erreur, aucun avertissement, les décomptes de lignes correspondent toujours.
Toujours mapper Oracle DATE à PostgreSQL TIMESTAMP.

C'est la corruption silencieuse de données la plus courante lors des migrations d'Oracle vers PostgreSQL.

L'importation réussit.

Les comptes de lignes correspondent.

Les données sont erronées.

Le mappage correct est toujours Oracle DATE vers PostgreSQL TIMESTAMP.

Selon le guide de migration d'AWS, Oracle vers Aurora PostgreSQL, ceci est un mappage obligatoire, pas facultatif.

ora2pg gère cela correctement par défaut.

Deux problèmes de suivi à vérifier après le remappage :

TRUNC(date) en SQL et PL/SQL.
Les développeurs Oracle utilisent fréquemment TRUNC(SYSDATE) pour supprimer la composante temporelle et obtenir minuit.
Sous PostgreSQL, l'équivalent est DATE_TRUNC('day', now()).
Toutes les requêtes ou procédures stockées utilisant TRUNC d'Oracle sur une colonne de date doivent être mises à jour.

Comparaisons de dates au niveau applicatif.
Code qui compare des dates avec = TRUNC(SYSDATE) (vérification des enregistrements d’aujourd’hui) se comporte différemment une fois que la colonne est TIMESTAMP.
Vérifiez toute la logique de comparaison de dates dans le code de l'application, pas seulement dans la base de données.

Comment mapper un Oracle NUMBER pour de meilleures performances ?

N'acceptez jamais NUMERIC pour toutes les colonnes NUMBER.
NUMERIC utilise l'arithmétique à précision arbitraire — correcte mais lente pour les charges de travail entières.
Analysez la plage et l'échelle réelles de chaque colonne : utilisez INTEGER pour les valeurs jusqu'à 2,1 milliards, BIGINT pour la plage 64 bits, NUMERIC(p,s) pour une précision décimale fixe.
Modifier les valeurs par défaut d'ora2pg avec la directive MODIFY_TYPE.

La bonne approche est d'abord d'analyser vos données réelles :

NUMBER(p, 0) où les valeurs rentrent dans 32 bits : utilisez INTEGER (jusqu'à 2,1 milliards)
NUMBER(p, 0) où les valeurs s'inscrivent dans 64 bits : utilisez BIGINT (jusqu'à 9,2 × 10¹⁸)
NOMBRE(p, s) avec une précision décimale fixe : utilisez NUMERIC(p, s)
NOMBRE sans précision (calculs flottants) : considérez DOUBLE PRECISION pour la vitesse, NUMERIC pour l'exactitude

AWS a publié une analyse en deux parties sur la façon d'évaluer les colonnes Oracle NUMBER et d'attribuer le bon type PostgreSQL.

Le processus consiste à interroger Oracle pour trouver la distribution réelle des minimum, maximum et de l'échelle pour chaque colonne avant de prendre une décision.

Dans ora2pg, vous remplacez la valeur par défaut par le MODIFICATION_TYPE directive dans votre fichier de configuration :

MODIFY_TYPE COMMANDES.MONTANT_TOTAL:numérique(15,2),COMMANDES.CODE_STATUT:entier

Ceci est au niveau de la colonne.

Vous le définissez par table après avoir analysé le profil des données réelles de chaque colonne.

Sauter cette étape et accepter NUMERIC partout est l'une des causes les plus courantes de mauvaises performances de PostgreSQL immédiatement après une migration.

Qu'advient-il des CLOB et BLOB d'Oracle ?

Oracle CLOB est mappé à PostgreSQL TEXT, qui gère des valeurs allant jusqu'à environ 1 Go via le stockage TOAST — aucune modification de l'application n'est nécessaire pour les données elles-mêmes.

Les BLOB Oracle correspondent à BYTEA pour la plupart des cas d'utilisation, ou au type lo pour le streaming.

Les travaux de migration réécrivent les appels au package DBMS_LOB ; il n'y a pas d'équivalent PostgreSQL.

L'exception concerne le code d'application qui utilise le package DBMS_LOB d'Oracle : DBMS_LOB.READ, DBMS_LOB.WRITE, DBMS_LOB.GETLENGTH, et ainsi de suite.

Ce sont des API LOB spécifiques à Oracle.

Ils doivent être réécrits pour utiliser les fonctions de chaîne SQL standard dans PostgreSQL.

Il n'y a pas d'équivalent à DBMS_LOB dans PostgreSQL.

BLOB est plus nuancé.

PostgreSQL a deux options :

BYTEA stocke les données binaires en ligne dans la ligne, jusqu'à environ 1 Go.
C'est plus simple et fonctionne bien pour les images, les documents et le contenu binaire de petite à moyenne taille.

Grands Objets (type lo) stocker des données dans le catalogue système pg_largeobject et prendre en charge l'accès en flux via lo_read et lo_write.
Ceci est plus proche de la sémantique des BLOB Oracle pour les applications qui diffusent du contenu binaire par morceaux.
Les gros objets nécessitent une gestion explicite de leur cycle de vie : vous devez appeler lo_unlink() pour les supprimer, ou utiliser l'extension lo pour gérer cela automatiquement via des déclencheurs.

Pour la plupart des migrations, BYTEA est le bon choix, sauf si vous avez une exigence de streaming spécifique.

Cinq pièges de type qui surprennent les équipes

Ceux-ci n'apparaissent pas toujours dans les rapports automatisés de conversion de schéma.

Ils cassent des choses à l'exécution, souvent dans des cas limites qui ne sont pas couverts par les tests de base.

1. Une chaîne vide est égale à NULL dans Oracle.
Oracle VARCHAR2 traite une chaîne vide ('' ) comme NULL.
PostgreSQL les traite comme des valeurs différentes.
Toute logique d'application ou SQL qui repose sur colonne EST NUL pour attraper à la fois NULL et des chaînes vides cela échouera silencieusement.
Toute contrainte NOT NULL sur une colonne VARCHAR2 dans Oracle n'empêche pas les chaînes vides.
Dans PostgreSQL, il empêche les chaînes vides.
Auditer chaque contrainte NOT NULL et chaque EST NUL / N'EST PAS NUL Vérifiez dans votre base de code avant la bascule.

2. VARCHAR2 sémantique par octet vs par caractère.
VARCHAR2(20 octets) et VARCHAR2(20 caractères) sont différents dans Oracle.
PostgreSQL VARCHAR(n) signifie toujours des caractères.
Une colonne définie comme VARCHAR2(20 OCTETS) dans une base de données Oracle à caractère unique peut contenir jusqu'à 20 caractères.
La même colonne migrée avec le codage AL32UTF8 pourrait contenir moins de caractères si des caractères multibytes sont présents.
Lors de la migration de bases de données Oracle utilisant WE8MSWIN1252 ou des jeux de caractères à octet unique similaires, vérifiez explicitement les longueurs de colonnes après la conversion.

3. BOOLEAN avant Oracle 23c.
Oracle n'avait pas de type BOOLEAN natif en SQL avant la version 23c (sortie en 2023).
Les solutions de contournement courantes étaient NUMBER(1) avec une contrainte de vérification (0 pour faux, 1 pour vrai) ou CHAR(1) (‘Y’/‘N’).
Ni l'un ni l'autre n'est automatiquement détecté comme candidat booléen par ora2pg.
Vous devez identifier manuellement ces colonnes et configurer MODIFICATION_TYPE pour les mapper au type BOOLEAN de PostgreSQL, ainsi que pour mettre à jour toute logique applicative qui insère des valeurs 0/1 ou ‘Y’/‘N’.

4. ROWID et UROWID.
Les applications Oracle utilisent parfois le ROWID pour récupérer rapidement des lignes : stockez le ROWID d'une ligne, puis utilisez-le pour récupérer rapidement cette ligne plus tard.
L'identifiant physique de ligne de PostgreSQL (ctid) n'est pas stable.
Il change lorsqu'une ligne est mise à jour, lorsqu'un VACUUM FULL est exécuté, ou lorsqu'une table est groupée.
Toute logique d'application qui stocke et réutilise des ROWIDs doit être repensée avec une clé primaire appropriée.

5. Sémantique de stockage de TIMESTAMP WITH TIME ZONE.
Oracle TIMESTAMP WITH TIME ZONE stocke le décalage d'origine (par exemple, 2024-03-15 14:30:00 +02:00).
TIMESTAMPTZ de PostgreSQL est toujours converti en UTC en interne et applique le fuseau horaire de la session lors de l'affichage.
La valeur semble identique en sortie, mais la représentation interne diffère.
Ceci est important lorsque votre application lit directement les décalages de fuseau horaire de la base de données, ou lorsque vous comparez des valeurs entre des sessions avec des paramètres de fuseau horaire différents.
Testez explicitement les requêtes sensibles au fuseau horaire.

Vous n'êtes pas sûr de celles de ces pièges qui s'appliquent à votre schéma ?
Je propose une évaluation de migration à prix fixe qui examine vos types de données, le volume de PL/SQL et les dépendances SQL de l'application — et livre un registre des risques écrit avant le début de tout travail de migration.
Voir ce que couvre l'évaluation

Qu'est-ce qu'ora2pg gère automatiquement ?

ora2pg mappe correctement DATE à TIMESTAMP, CLOB et NCLOB à TEXT, BLOB et RAW à BYTEA, BINARY_FLOAT à REAL, BINARY_DOUBLE à DOUBLE PRECISION, et XMLTYPE à XML.
Ce qu'il ne prend pas en charge : optimisation des nombres vers des types entiers, détection de colonnes booléennes, différences d'encodage VARCHAR2 et réécritures de DBMS_LOB.
Ceux-ci nécessitent une configuration manuelle de MODIFY_TYPE.

Ce qu'ora2pg ne gère pas automatiquement :

nombre en types entiers. Toutes les colonnes de type NOMBRE sont par défaut de type NUMÉRIQUE. Vous devez utiliser le MODIFICATION_TYPE directive de substituer au niveau de la colonne.
Détection booléenne. Les colonnes NUMBER(1) et CHAR(1) utilisées comme booléens ne sont pas détectées automatiquement. Une configuration manuelle est requise.
Sémantique des octets par rapport à la sémantique des caractères VARCHAR2. ora2pg n'ajuste pas la longueur des colonnes pour les différences d'encodage. Une révision manuelle est nécessaire pour les bases de données sources non UTF8.
Colonnes ROWID. ora2pg signale ces éléments mais ne les remplace pas. Une refonte manuelle est nécessaire.
Appels DBMS_LOB en PL/SQL. ora2pg convertit la structure PL/SQL mais ne réécrit pas les appels aux API LOB. Ceux-ci nécessitent un remplacement manuel.

Le documentation officielle ora2pg couvre la référence complète de configuration.

Le TYPE_DE_DONNÉES la directive permet le remappage de types global.

MODIFICATION_TYPE permet des remplacements au niveau des colonnes.

La liste de contrôle de décision des types de données

Avant d'exécuter votre conversion de schéma, parcourez cette liste :

Identifier toutes les colonnes de type Oracle DATE et confirmer leur mappage en TIMESTAMP
Interroger chaque colonne NUMÉRIQUE pour déterminer la plage et l'échelle réelles
Définir les remplacements MODIFY_TYPE pour les colonnes NUMBER(p,0) de type entier.
Identifier les colonnes booléennes NUMBER(1) et CHAR(1) et configurer MODIFY_TYPE
Vérifier le jeu de caractères source Oracle pour la sémantique octet ou caractère VARCHAR2
Auditer tout EST NUL vérifier dans le code de l'application la gestion des chaînes vides
Lister toutes les utilisations de ROWID dans le code de l'application et concevoir des remplacements de clés primaires
Identifier les appels DBMS_LOB en PL/SQL et planifier des réécritures
Testez les requêtes TIMESTAMP WITH TIME ZONE avec différents paramètres de fuseau horaire de session

Obtenir la correspondance des types correctement avant de commencer permet d'éviter des reprises de travail importantes après la migration des données.

Une évaluation de migration attrape la plupart de ceux-ci avant le début du projet.

Foire aux questions

Ora2pg gère-t-il automatiquement toutes les conversions de types de données Oracle ?

ora2pg gère la plupart des conversions courantes correctement, y compris DATE vers TIMESTAMP, CLOB vers TEXT et BLOB vers BYTEA.
Il n'optimise pas automatiquement NUMBER en types entiers, ne détecte pas les colonnes booléennes à partir des solutions de contournement NUMBER(1) ou CHAR(1), ni n'ajuste les longueurs VARCHAR2 pour la sémantique octet par caractère.
Ceux-ci nécessitent une configuration manuelle à l'aide de la directive MODIFY_TYPE dans ora2pg.conf.

La date PostgreSQL équivalente à Oracle DATE est TIMESTAMP.

L'équivalent PostgreSQL correct pour Oracle DATE est TIMESTAMP, pas DATE.
Oracle DATE stocke la date et l'heure (année, mois, jour, heure, minute, seconde).
PostgreSQL DATE ne stocke que la date.
La mise en correspondance d'une Oracle DATE avec une PostgreSQL DATE supprime silencieusement le composant temporel de chaque valeur.
C'est l'un des problèmes de corruption silencieuse des données les plus courants lors des migrations d'Oracle vers PostgreSQL.

Comment convertir efficacement les colonnes Oracle NUMBER en PostgreSQL ?

Analysez la plage et l'échelle des données actuelles pour chaque colonne NUMÉRIQUE avant de procéder au mappage.
Pour les colonnes dont l'échelle est 0 (entiers) et qui tiennent dans 32 bits, utilisez INTEGER.
Pour les colonnes dans la plage de 64 bits, utilisez BIGINT.
Pour une précision décimale fixe, utilisez NUMERIC(p,s).
RESERVER NUMERIC sans précision pour les colonnes qui nécessitent réellement une précision arbitraire.
Utilisez la directive ora2pg’s MODIFY_TYPE pour configurer les remplacements au niveau des colonnes.
Tout mettre par défaut sur NUMERIC est correct mais entraîne une dégradation mesurable des performances sur les charges de travail entières.

Que devient le BOOLEAN d'Oracle dans PostgreSQL ?

Oracle n'avait pas de type d'intégralité SQL natif avant la version 23c.
Les schémas Oracle avant la version 23c utilisaient généralement NUMBER(1) (0/1) ou CHAR(1) (‘O’/‘N’) comme solutions de contournement pour les booléens.
Ceux-ci ne sont pas automatiquement convertis en BOOLEAN PostgreSQL par les outils de migration.
Vous devez identifier manuellement ces colonnes, configurer MODIFY_TYPE dans ora2pg pour les mapper à BOOLEAN, et mettre à jour le code de l'application qui insère ou lit les valeurs 0/1 ou ‘Y’/‘N’.
Les bases de données Oracle 23c avec des colonnes BOOLEAN natives se traduisent directement en BOOLEAN PostgreSQL.

PostgreSQL peut-il gérer les données CLOB d'Oracle ?

Oui.
TEXT dans PostgreSQL est le mappage correct pour CLOB dans Oracle.
Le type TEXT de PostgreSQL n'a pas de limite de taille stricte ; les valeurs allant jusqu'à environ 1 Go sont gérées automatiquement via le stockage TOAST.
La migration des données elle-même est simple.
Le travail consiste à réécrire tout code d'application qui utilise le package DBMS_LOB d'Oracle (DBMS_LOB.READ, DBMS_LOB.WRITE, DBMS_LOB.GETLENGTH, etc.), car ces API LOB spécifiques à Oracle n'ont pas d'équivalent direct dans PostgreSQL et doivent être remplacées par des fonctions de chaîne SQL standard.

En résumé

La traduction des types de données Oracle en PostgreSQL n'est pas un exercice univoque.

La plupart des types se traduisent proprement.

Une poignée — DATE, NUMBER, chaînes vides, solutions de contournement BOOLEAN et ROWID — nécessite des décisions délibérées et une configuration manuelle.

Les deux règles qui empêchent la plupart des problèmes de données post-migration :

Ne jamais mapper Oracle DATE à PostgreSQL DATE. Utiliser toujours TIMESTAMP.
N'acceptez jamais NUMERIC pour toutes les colonnes NUMBER. Analysez les plages et utilisez des types entiers lorsque cela est approprié.

Si vous prévoyez une migration d'Oracle vers PostgreSQL et que vous souhaitez définir la stratégie de type de données dès le départ, prendre contact →