{"id":6608,"date":"2026-04-14T23:06:24","date_gmt":"2026-04-14T21:06:24","guid":{"rendered":"https:\/\/rootfan.com\/?p=6608"},"modified":"2026-04-14T23:06:27","modified_gmt":"2026-04-14T21:06:27","slug":"mapeo-de-tipos-de-datos-de-oracle-a-postgresql","status":"publish","type":"post","link":"https:\/\/rootfan.com\/es\/oracle-to-postgresql-data-type-mapping\/","title":{"rendered":"La Gu\u00eda Completa de Mapeo de Tipos de Datos de Oracle a PostgreSQL"},"content":{"rendered":"<p><strong>En resumen<\/strong> La mayor\u00eda de los mapeos de tipos de datos de Oracle a PostgreSQL son sencillos.<br>Los peligrosos no lo son.<br>Oracle DATE incluye un componente de tiempo que PostgreSQL DATE descarta silenciosamente.<br>Oracle NUMBER se mapea a NUMERIC por defecto, lo cual es correcto pero lento para cargas de trabajo enteras.<br>Las cadenas vac\u00edas en Oracle son NULL; en PostgreSQL no lo son.<br>Esta gu\u00eda te ofrece la tabla de correspondencia completa y cubre las cinco trampas que causan corrupci\u00f3n silenciosa de datos durante la migraci\u00f3n.<\/p>\n\n\n\n<!--more-->\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>The most common data problem in an Oracle to PostgreSQL data type mapping exercise isn\u2019t a missing column or a failed import.<\/p>\n\n\n\n<p>It\u2019s a mapping that looks correct, passes validation, and silently changes your data.<\/p>\n\n\n\n<p>Migration projects where dates lost their time component, integer arithmetic slowed to a crawl, and application logic broke because empty strings stopped behaving like NULL \u2014 none of these were obvious at the schema conversion stage.<\/p>\n\n\n\n<p>All of them were preventable.<\/p>\n\n\n\n<p>This guide gives you the complete mapping table and explains the decisions that matter.<\/p>\n\n\n\n<p>If you\u2019re at the start of your <a href=\"\/es\/por-que-las-empresas-estan-abandonando-oracle-por-postgresql\/\">Oracle to PostgreSQL migration<\/a>, bookmark this page.<\/p>\n\n\n\n<p>You\u2019ll come back to it.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>\u00cdndice<\/h2><nav><ul><li><a href=\"#the-complete-oracle-to-postgre-sql-data-type-mapping-table\">The Complete Oracle to PostgreSQL Data Type Mapping Table<\/a><\/li><li><a href=\"#why-does-oracle-date-break-everything-in-postgre-sql\">Why Does Oracle DATE Break Everything in PostgreSQL?<\/a><\/li><li><a href=\"#how-should-you-map-oracle-number-for-performance\">How Should You Map Oracle NUMBER for Performance?<\/a><\/li><li><a href=\"#what-happens-to-oracle-clob-and-blob\">What Happens to Oracle CLOB and BLOB?<\/a><\/li><li><a href=\"#five-type-traps-that-catch-teams-off-guard\">Five Type Traps That Catch Teams Off Guard<\/a><\/li><li><a href=\"#what-does-ora-2-pg-handle-automatically\">What Does ora2pg Handle Automatically?<\/a><\/li><li><a href=\"#the-data-type-decision-checklist\">The Data Type Decision Checklist<\/a><\/li><li><a href=\"#frequently-asked-questions\">Preguntas frecuentes<\/a><ul><li><a href=\"#faq-question-1775170973711\">Does ora2pg handle all Oracle data type conversions automatically?<\/a><\/li><li><a href=\"#faq-question-1775170974711\">What is the PostgreSQL equivalent of Oracle DATE?<\/a><\/li><li><a href=\"#faq-question-1775170975711\">How do I convert Oracle NUMBER columns efficiently in PostgreSQL?<\/a><\/li><li><a href=\"#faq-question-1775170976711\">What happens to Oracle BOOLEAN in PostgreSQL?<\/a><\/li><li><a href=\"#faq-question-1775170977711\">Can PostgreSQL handle Oracle CLOB data?<\/a><\/li><\/ul><\/li><li><a href=\"#in-summary\">En resumen<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-complete-oracle-to-postgre-sql-data-type-mapping-table\">The Complete Oracle to PostgreSQL Data Type Mapping Table<\/h2>\n\n\n\n<p>The table below covers every common Oracle type.<\/p>\n\n\n\n<p>The \u201cSafe Default\u201d column is what ora2pg uses automatically.<\/p>\n\n\n\n<p>The \u201cRecommended\u201d column is what you should actually use after analysing your data.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Oracle Type<\/th><th>Safe Default<\/th><th>Recommended<\/th><th>Notes<\/th><\/tr><\/thead><tbody><tr><td>NUMBER<\/td><td>NUMERIC<\/td><td>INTEGER, BIGINT, or NUMERIC<\/td><td>Depends on scale and range \u2014 see section below<\/td><\/tr><tr><td>NUMBER(p, 0)<\/td><td>NUMERIC(p)<\/td><td>SMALLINT \/ INTEGER \/ BIGINT<\/td><td>Use integer types for better performance<\/td><\/tr><tr><td>NUMBER(p, s)<\/td><td>NUMERIC(p, s)<\/td><td>NUMERIC(p, s)<\/td><td>Exact match<\/td><\/tr><tr><td>VARCHAR2(n)<\/td><td>VARCHAR(n)<\/td><td>VARCHAR(n) or TEXT<\/td><td>n is characters in PG; may need adjustment for byte-semantics columns<\/td><\/tr><tr><td>NVARCHAR2(n)<\/td><td>VARCHAR(n)<\/td><td>VARCHAR(n)<\/td><td>PG is always UTF-8; no separate unicode type needed<\/td><\/tr><tr><td>CHAR(n)<\/td><td>CHAR(n)<\/td><td>CHAR(n)<\/td><td>Direct match<\/td><\/tr><tr><td>DATE<\/td><td>TIMESTAMP<\/td><td>TIMESTAMP<\/td><td>Oracle DATE includes time \u2014 never map to PG DATE<\/td><\/tr><tr><td>TIMESTAMP<\/td><td>TIMESTAMP<\/td><td>TIMESTAMP<\/td><td>Direct match<\/td><\/tr><tr><td>TIMESTAMP WITH TIME ZONE<\/td><td>TIMESTAMPTZ<\/td><td>TIMESTAMPTZ<\/td><td>Storage semantics differ \u2014 see section below<\/td><\/tr><tr><td>TIMESTAMP WITH LOCAL TIME ZONE<\/td><td>TIMESTAMPTZ<\/td><td>TIMESTAMPTZ<\/td><td>Same caveat<\/td><\/tr><tr><td>INTERVAL YEAR TO MONTH<\/td><td>INTERVAL<\/td><td>INTERVAL<\/td><td>PG INTERVAL is a single flexible type<\/td><\/tr><tr><td>INTERVAL DAY TO SECOND<\/td><td>INTERVAL<\/td><td>INTERVAL<\/td><td>Test date arithmetic across month\/day boundaries<\/td><\/tr><tr><td>CLOB<\/td><td>TEXT<\/td><td>TEXT<\/td><td>PG TEXT is unlimited in practice (up to ~1 GB via TOAST)<\/td><\/tr><tr><td>NCLOB<\/td><td>TEXT<\/td><td>TEXT<\/td><td>Mismo<\/td><\/tr><tr><td>BLOB<\/td><td>BYTEA<\/td><td>BYTEA or lo<\/td><td>lo for large files or streaming use cases<\/td><\/tr><tr><td>RAW(n)<\/td><td>BYTEA<\/td><td>BYTEA<\/td><td>Direct replacement<\/td><\/tr><tr><td>LONG RAW<\/td><td>BYTEA<\/td><td>BYTEA<\/td><td>Deprecated in Oracle; migrate promptly<\/td><\/tr><tr><td>LONG<\/td><td>TEXT<\/td><td>TEXT<\/td><td>Deprecated in Oracle<\/td><\/tr><tr><td>XMLTYPE<\/td><td>XML<\/td><td>XML<\/td><td>PG XML querying uses XPATH() and XMLTABLE()<\/td><\/tr><tr><td>BINARY_FLOAT<\/td><td>REAL<\/td><td>REAL<\/td><td>4-byte IEEE 754 \u2014 direct match<\/td><\/tr><tr><td>BINARY_DOUBLE<\/td><td>DOUBLE PRECISION<\/td><td>DOUBLE PRECISION<\/td><td>8-byte IEEE 754 \u2014 direct match<\/td><\/tr><tr><td>BOOLEAN (Oracle 23c+)<\/td><td>BOLEANO<\/td><td>BOLEANO<\/td><td>Pre-23c: NUMBER(1) or CHAR(1) \u2014 manual conversion required<\/td><\/tr><tr><td>ROWID \/ UROWID<\/td><td>No equivalent<\/td><td>Redesign with primary key<\/td><td>PG ctid is not stable across VACUUM or clustering<\/td><\/tr><tr><td>SDO_GEOMETRY<\/td><td>No built-in<\/td><td>PostGIS GEOMETRY<\/td><td>Requires PostGIS extension<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"why-does-oracle-date-break-everything-in-postgre-sql\">Why Does Oracle DATE Break Everything in PostgreSQL?<\/h2>\n\n\n\n<p>Oracle DATE almacena fecha y hora. PostgreSQL DATE almacena solo fecha.<br>Mapping Oracle DATE to PostgreSQL DATE silently discards the time component of every value in that column \u2014 no error, no warning, row counts still match.<br>Siempre mapea Oracle DATE a PostgreSQL TIMESTAMP.<\/p>\n\n\n\n<p>This is the most common silent data corruption in Oracle to PostgreSQL migrations.<\/p>\n\n\n\n<p>The import succeeds.<\/p>\n\n\n\n<p>Row counts match.<\/p>\n\n\n\n<p>The data is wrong.<\/p>\n\n\n\n<p>The correct mapping is always Oracle DATE to PostgreSQL TIMESTAMP.<\/p>\n\n\n\n<p><a href=\"https:\/\/docs.aws.amazon.com\/dms\/latest\/oracle-to-aurora-postgresql-migration-playbook\/chap-oracle-aurora-pg.tables.common.html\" rel=\"nofollow noopener\" target=\"_blank\">According to the AWS Oracle to Aurora PostgreSQL Migration Playbook<\/a>, this is a required mapping, not optional.<\/p>\n\n\n\n<p>ora2pg gets this right by default.<\/p>\n\n\n\n<p>Two follow-on issues to check after remapping:<\/p>\n\n\n\n<p><strong>TRUNC(date) in SQL and PL\/SQL.<\/strong><br>Oracle developers frequently use <code>TRUNC(sysdate)<\/code> to strip the time component and get midnight.<br>In PostgreSQL the equivalent is <code>DATE_TRUNC('day', now())<\/code>.<br>Any queries or stored procedures using Oracle\u2019s TRUNC on a date column need to be updated.<\/p>\n\n\n\n<p><strong>Application-layer date comparisons.<\/strong><br>Code that compares dates with <code>= TRUNC(sysdate)<\/code> (checking for today\u2019s records) behaves differently once the column is TIMESTAMP.<br>Review all date comparison logic in application code, not just in the database.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"how-should-you-map-oracle-number-for-performance\">How Should You Map Oracle NUMBER for Performance?<\/h2>\n\n\n\n<p>Never accept NUMERIC for all NUMBER columns.<br>NUMERIC uses arbitrary-precision arithmetic \u2014 correct but slow for integer workloads.<br>Analyse each column\u2019s actual range and scale: use INTEGER for values up to 2.1 billion, BIGINT for 64-bit range, NUMERIC(p,s) for fixed decimal precision.<br>Override ora2pg defaults with the MODIFY_TYPE directive.<\/p>\n\n\n\n<p>The right approach is to analyse your actual data first:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>NUMBER(p, 0)<\/strong> where values fit in 32 bits: use INTEGER (up to 2.1 billion)<\/li>\n\n\n\n<li><strong>NUMBER(p, 0)<\/strong> where values fit in 64 bits: use BIGINT (up to 9.2 \u00d7 10\u00b9\u2078)<\/li>\n\n\n\n<li><strong>NUMBER(p, s)<\/strong> with fixed decimal precision: use NUMERIC(p, s)<\/li>\n\n\n\n<li><strong>NUMBER<\/strong> with no precision (floating calculations): consider DOUBLE PRECISION for speed, NUMERIC for exactness<\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/aws.amazon.com\/blogs\/database\/convert-the-number-data-type-from-oracle-to-postgresql-part-1\/\" rel=\"nofollow noopener\" target=\"_blank\">AWS published a two-part analysis<\/a> on how to assess Oracle NUMBER columns and assign the right PostgreSQL type.<\/p>\n\n\n\n<p>The process involves querying Oracle to find the actual min, max, and scale distribution for each column before deciding.<\/p>\n\n\n\n<p>In ora2pg, you override the default with the <code>MODIFICAR_TIPO<\/code> directive in your configuration file:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>MODIFY_TYPE  ORDERS.TOTAL_AMOUNT:numeric(15,2),ORDERS.STATUS_CODE:integer<\/code><\/pre>\n\n\n\n<p>This is column-level.<\/p>\n\n\n\n<p>You set it per table after analysing each column\u2019s real data profile.<\/p>\n\n\n\n<p>Skipping this step and accepting NUMERIC everywhere is one of the most common causes of poor PostgreSQL performance immediately after migration.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-happens-to-oracle-clob-and-blob\">What Happens to Oracle CLOB and BLOB?<\/h2>\n\n\n\n<p>Oracle CLOB maps to PostgreSQL TEXT, which handles values up to approximately 1 GB via TOAST storage \u2014 no application changes needed for the data itself.<\/p>\n\n\n\n<p>Oracle BLOB maps to BYTEA for most use cases, or the lo type for streaming.<\/p>\n\n\n\n<p>The migration work is rewriting DBMS_LOB package calls; there is no PostgreSQL equivalent.<\/p>\n\n\n\n<p>The exception is application code that uses Oracle\u2019s DBMS_LOB package: DBMS_LOB.READ, DBMS_LOB.WRITE, DBMS_LOB.GETLENGTH, and so on.<\/p>\n\n\n\n<p>These are Oracle-specific LOB APIs.<\/p>\n\n\n\n<p>They must be rewritten to use standard SQL string functions in PostgreSQL.<\/p>\n\n\n\n<p>There is no DBMS_LOB equivalent in PostgreSQL.<\/p>\n\n\n\n<p>BLOB is more nuanced.<\/p>\n\n\n\n<p>PostgreSQL has two options:<\/p>\n\n\n\n<p><strong>BYTEA<\/strong> stores binary data inline in the row, up to approximately 1 GB.<br>It\u2019s simpler and works well for images, documents, and small-to-medium binary content.<\/p>\n\n\n\n<p><strong>Large Objects (lo type)<\/strong> store data in the pg_largeobject system catalog and support streaming access via lo_read and lo_write.<br>This is closer to Oracle BLOB semantics for applications that stream binary content in chunks.<br>Large Objects require explicit lifecycle management: you must call lo_unlink() to delete them, or use the lo extension to handle this automatically via triggers.<\/p>\n\n\n\n<p>For most migrations, BYTEA is the right choice unless you have a specific streaming requirement.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"five-type-traps-that-catch-teams-off-guard\">Five Type Traps That Catch Teams Off Guard<\/h2>\n\n\n\n<p>These don\u2019t always show up in automated schema conversion reports.<\/p>\n\n\n\n<p>They break things at runtime, often in edge cases that aren\u2019t covered by basic testing.<\/p>\n\n\n\n<p><strong>1. Empty string equals NULL in Oracle.<\/strong><br>Oracle VARCHAR2 treats an empty string (<code>'' <\/code>) como NULL.<br>PostgreSQL treats them as different values.<br>Any SQL or application logic that relies on <code>column IS NULL<\/code> to catch both NULL and empty strings will break silently.<br>Any NOT NULL constraint on a VARCHAR2 column in Oracle does not prevent empty strings.<br>In PostgreSQL, it does prevent empty strings.<br>Audit every NOT NULL constraint and every <code>IS NULL<\/code> \/ <code>IS NOT NULL<\/code> check in your codebase before cutover.<\/p>\n\n\n\n<p><strong>2. VARCHAR2 byte vs character semantics.<\/strong><br><code>VARCHAR2(20 BYTE)<\/code> y <code>VARCHAR2(20 CHAR)<\/code> are different in Oracle.<br>PostgreSQL VARCHAR(n) always means characters.<br>A column defined as VARCHAR2(20 BYTE) in a single-byte Oracle database may hold up to 20 characters.<br>The same column migrated with AL32UTF8 encoding could hold fewer characters if multibyte characters are present.<br>When migrating from Oracle databases using WE8MSWIN1252 or similar single-byte character sets, verify column lengths explicitly after conversion.<\/p>\n\n\n\n<p><strong>3. BOOLEAN before Oracle 23c.<\/strong><br>Oracle had no native BOOLEAN type in SQL before version 23c (released in 2023).<br>Common workarounds were NUMBER(1) with a check constraint (0 for false, 1 for true) or CHAR(1) (\u2018Y\u2019\/\u2018N\u2019).<br>Neither is automatically detected as a boolean candidate by ora2pg.<br>You need to identify these columns manually and configure <code>MODIFICAR_TIPO<\/code> to map them to PostgreSQL BOOLEAN, along with updating any application logic that inserts 0\/1 or \u2018Y\u2019\/\u2018N\u2019 values.<\/p>\n\n\n\n<p><strong>4. ROWID and UROWID.<\/strong><br>Oracle applications sometimes use ROWID for fast row re-fetch: store the ROWID of a row, then use it to retrieve that row quickly later.<br>PostgreSQL\u2019s physical row identifier (ctid) is not stable.<br>It changes when a row is updated, when VACUUM FULL runs, or when a table is clustered.<br>Any application logic that stores and reuses ROWIDs must be redesigned with a proper primary key.<\/p>\n\n\n\n<p><strong>5. TIMESTAMP WITH TIME ZONE storage semantics.<\/strong><br>Oracle TIMESTAMP WITH TIME ZONE stores the original offset (for example, <code>2024-03-15 14:30:00 +02:00<\/code>).<br>PostgreSQL TIMESTAMPTZ always converts to UTC internally and applies the session timezone on display.<br>The value appears identical in output but the internal representation differs.<br>This matters when your application reads timezone offsets from the database directly, or when you\u2019re comparing values across sessions with different timezone settings.<br>Test timezone-sensitive queries explicitly.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><strong>Not sure which of these traps apply to your schema?<\/strong><br>I offer a fixed-fee migration assessment that reviews your data types, PL\/SQL volume, and application SQL dependencies \u2014 and delivers a written risk register before any migration work starts.<br><a href=\"https:\/\/rootfan.com\/es\/servicios\/\">Ver qu\u00e9 cubre la evaluaci\u00f3n<\/a><\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-does-ora-2-pg-handle-automatically\">What Does ora2pg Handle Automatically?<\/h2>\n\n\n\n<p>ora2pg correctly maps DATE to TIMESTAMP, CLOB and NCLOB to TEXT, BLOB and RAW to BYTEA, BINARY_FLOAT to REAL, BINARY_DOUBLE to DOUBLE PRECISION, and XMLTYPE to XML.<br>What it does not handle: NUMBER optimisation to integer types, boolean column detection, VARCHAR2 encoding differences, and DBMS_LOB rewrites.<br>These require manual MODIFY_TYPE configuration.<\/p>\n\n\n\n<p>What ora2pg does not handle automatically:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>NUMBER to integer types.<\/strong> Every NUMBER column defaults to NUMERIC. You must use the <code>MODIFICAR_TIPO<\/code> directive to override at the column level.<\/li>\n\n\n\n<li><strong>Boolean detection.<\/strong> NUMBER(1) and CHAR(1) columns used as booleans are not auto-detected. Manual configuration required.<\/li>\n\n\n\n<li><strong>VARCHAR2 byte vs character semantics.<\/strong> ora2pg does not adjust column lengths for encoding differences. Manual review required for non-UTF8 source databases.<\/li>\n\n\n\n<li><strong>ROWID columns.<\/strong> ora2pg flags these but does not replace them. Manual redesign required.<\/li>\n\n\n\n<li><strong>DBMS_LOB calls in PL\/SQL.<\/strong> ora2pg converts PL\/SQL structure but does not rewrite LOB API calls. These require manual replacement.<\/li>\n<\/ul>\n\n\n\n<p>En <a href=\"https:\/\/ora2pg.darold.net\/documentation.html\" rel=\"nofollow noopener\" target=\"_blank\">official ora2pg documentation<\/a> covers the full configuration reference.<\/p>\n\n\n\n<p>En <code>DATA_TYPE<\/code> directive allows global type remapping.<\/p>\n\n\n\n<p><code>MODIFICAR_TIPO<\/code> allows column-level overrides.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-data-type-decision-checklist\">The Data Type Decision Checklist<\/h2>\n\n\n\n<p>Before running your schema conversion, work through this list:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Identify every Oracle DATE column and confirm it maps to TIMESTAMP<\/li>\n\n\n\n<li>Query each NUMBER column to determine actual value range and scale<\/li>\n\n\n\n<li>Set MODIFY_TYPE overrides for integer-range NUMBER(p,0) columns<\/li>\n\n\n\n<li>Identify NUMBER(1) and CHAR(1) boolean columns and configure MODIFY_TYPE<\/li>\n\n\n\n<li>Check the Oracle source character set for VARCHAR2 byte vs character semantics<\/li>\n\n\n\n<li>Audit every <code>IS NULL<\/code> check in application code for empty string handling<\/li>\n\n\n\n<li>List every ROWID usage in application code and design primary key replacements<\/li>\n\n\n\n<li>Identify DBMS_LOB calls in PL\/SQL and plan rewrites<\/li>\n\n\n\n<li>Test TIMESTAMP WITH TIME ZONE queries across different session timezone settings<\/li>\n<\/ul>\n\n\n\n<p>Getting the type mapping right before you start saves significant rework after data migration.<\/p>\n\n\n\n<p><a href=\"https:\/\/rootfan.com\/es\/servicios\/\">A migration assessment<\/a> catches most of these before the project begins.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"frequently-asked-questions\">Preguntas frecuentes<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<div class=\"rank-math-list\">\n<div id=\"faq-question-1775170973711\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Does ora2pg handle all Oracle data type conversions automatically?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>ora2pg handles most common conversions correctly, including DATE to TIMESTAMP, CLOB to TEXT, and BLOB to BYTEA.<br \/>It does not automatically optimise NUMBER to integer types, detect boolean columns from NUMBER(1) or CHAR(1) workarounds, or adjust VARCHAR2 lengths for byte vs character semantics.<br \/>These require manual configuration using the MODIFY_TYPE directive in ora2pg.conf.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1775170974711\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>What is the PostgreSQL equivalent of Oracle DATE?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>The correct PostgreSQL equivalent of Oracle DATE is TIMESTAMP, not DATE.<br \/>Oracle DATE stores both date and time (year, month, day, hour, minute, second).<br \/>PostgreSQL DATE stores only the date.<br \/>Mapping Oracle DATE to PostgreSQL DATE silently discards the time component of every value.<br \/>This is one of the most common silent data corruption issues in Oracle to PostgreSQL migrations.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1775170975711\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>How do I convert Oracle NUMBER columns efficiently in PostgreSQL?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Analyse the actual data range and scale for each NUMBER column before mapping.<br \/>For columns with scale 0 (integers) that fit within 32 bits, use INTEGER.<br \/>For columns within 64-bit range, use BIGINT.<br \/>For fixed decimal precision, use NUMERIC(p,s).<br \/>Reserve NUMERIC without precision for columns that genuinely require arbitrary precision.<br \/>Use ora2pg\u2019s MODIFY_TYPE directive to configure column-level overrides.<br \/>Defaulting everything to NUMERIC is correct but causes measurable performance degradation on integer workloads.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1775170976711\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>What happens to Oracle BOOLEAN in PostgreSQL?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Oracle had no native SQL BOOLEAN type before version 23c.<br \/>Pre-23c Oracle schemas typically used NUMBER(1) (0\/1) or CHAR(1) (\u2018Y\u2019\/\u2018N\u2019) as boolean workarounds.<br \/>These are not automatically converted to PostgreSQL BOOLEAN by migration tools.<br \/>You need to identify these columns manually, configure MODIFY_TYPE in ora2pg to map them to BOOLEAN, and update application code that inserts or reads 0\/1 or \u2018Y\u2019\/\u2018N\u2019 values.<br \/>Oracle 23c databases with native BOOLEAN columns map directly to PostgreSQL BOOLEAN.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1775170977711\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Can PostgreSQL handle Oracle CLOB data?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Yes.<br \/>PostgreSQL TEXT is the correct mapping for Oracle CLOB.<br \/>PostgreSQL TEXT has no hard size limit; values up to approximately 1 GB are handled automatically via TOAST storage.<br \/>The data migration itself is straightforward.<br \/>The work is in rewriting any application code that uses Oracle\u2019s DBMS_LOB package (DBMS_LOB.READ, DBMS_LOB.WRITE, DBMS_LOB.GETLENGTH, etc.), as these Oracle-specific LOB APIs have no direct PostgreSQL equivalent and must be replaced with standard SQL string functions.<\/p>\n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"in-summary\">En resumen<\/h2>\n\n\n\n<p>The Oracle to PostgreSQL data type mapping is not a one-to-one exercise.<\/p>\n\n\n\n<p>Most types map cleanly.<\/p>\n\n\n\n<p>A handful \u2014 DATE, NUMBER, empty strings, BOOLEAN workarounds, and ROWID \u2014 require deliberate decisions and manual configuration.<\/p>\n\n\n\n<p>The two rules that prevent most post-migration data problems:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Never map Oracle DATE to PostgreSQL DATE. Always use TIMESTAMP.<\/li>\n\n\n\n<li>Never accept NUMERIC for all NUMBER columns. Analyse ranges and use integer types where appropriate.<\/li>\n<\/ul>\n\n\n\n<p>If you\u2019re planning an Oracle to PostgreSQL migration and want to get the data type strategy right from the start, <a href=\"https:\/\/rootfan.com\/es\/servicios\/\">ponerse en contacto<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>","protected":false},"excerpt":{"rendered":"<p>TL;DR: Most Oracle-to-PostgreSQL data type mappings are straightforward.The dangerous ones are not.Oracle DATE includes a time component that PostgreSQL DATE silently discards.Oracle NUMBER maps to NUMERIC by default, which is correct but slow for integer workloads.Empty strings in Oracle are NULL; in PostgreSQL they are not.This guide gives you the full mapping table and covers &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/rootfan.com\/es\/oracle-to-postgresql-data-type-mapping\/\" class=\"more-link\">Seguir leyendo<span class=\"screen-reader-text\"> &#8220;The Complete Oracle-to-PostgreSQL Data Type Mapping Guide&#8221;<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":6619,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_focus_keyword":"oracle to postgresql data type mapping","rank_math_title":"The Complete Oracle-to-PostgreSQL Data Type Mapping Guide","rank_math_description":"Complete Oracle to PostgreSQL data type mapping reference. Covers NUMBER, VARCHAR2, DATE, CLOB, BLOB, and the five dangerous mappings that cause silent data corruption during migration.","rank_math_robots":"","rank_math_og_title":"","rank_math_og_description":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[31,126],"tags":[143,141,137],"class_list":["post-6608","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","category-postgresql","tag-data-migration","tag-data-types","tag-migration"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/rootfan.com\/wp-content\/uploads\/pexels-photo-36043291.jpeg?fit=1880%2C1253&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts\/6608","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/comments?post=6608"}],"version-history":[{"count":7,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts\/6608\/revisions"}],"predecessor-version":[{"id":6741,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts\/6608\/revisions\/6741"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/media\/6619"}],"wp:attachment":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/media?parent=6608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/categories?post=6608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/tags?post=6608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}