NUMBER Data Type - Oracle to PostgreSQL Migration

In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers.

In PostgreSQL, you can use SMALLINT, INT and BIGINT for integers, DECIMAL or NUMERIC for fixed point numbers, REAL and DOUBLE PRECISION for approximate floating point numbers.

Oracle:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 NUMBER(5),           -- Integer 
    cnt2 NUMBER(15),
    cnt3 NUMBER(15, 2),     -- Fixed point number
    cnt4 NUMBER,               -- Exact floating point number
    cnt5 NUMBER(*)
  );

PostgreSQL:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 INT,                        -- Integer 
    cnt2 BIGINT,
    cnt3 DECIMAL(15, 2),     -- Fixed point number
    cnt4 DECIMAL,               -- Exact floating point number (see notes below)
    cnt5 DECIMAL
  );

Conversion summary:

Oracle		PostgreSQL
NUMBER(p)	8-bit integer, 1 ⇐ p < 3	SMALLINT
NUMBER(p)	16-bit integer, 3 ⇐ p < 5	SMALLINT
NUMBER(p)	32-bit integer, 5 ⇐ p < 9	INT
NUMBER(p)	64-bit integer, 9 ⇐ p < 19	BIGINT
NUMBER(p)	Large integer, 19 ⇐ p ⇐ 38	DECIMAL(p)
NUMBER(p,s)	Fixed-point number, s > 0	DECIMAL(p,s)
NUMBER, NUMBER(*)	Exact floating-point number	DECIMAL	DOUBLE PRECISION (see notes below)

The NUMBER or NUMBER(*) data type without precision and scale can store exactly any arbitrary number. At the same time the PostgreSQL DOUBLE PRECISION is an inexact (approximate) data type:

PostgreSQL:

  -- Sum 0.1 + 0.1 + 0.1 gives 0.30000000000000004, not 0.3
  SELECT SUM(c1)
  FROM 
    (
       SELECT 0.1::DOUBLE PRECISION c1
       UNION ALL
       SELECT 0.1::DOUBLE PRECISION c1
       UNION ALL
       SELECT 0.1::DOUBLE PRECISION c1
     ) t;
 
     # sum
     # ---------------------
     # 0.30000000000000004

At the same time, if PostgreSQL DECIMAL is used without the precision and scale it can also store exactly any arbitrary number. This behaviour differs from ANSI SQL where s is 0 by default:

  -- Sum is correct now
  SELECT SUM(c1)
  FROM 
    (
       SELECT 0.1::DECIMAL c1
       UNION ALL
       SELECT 0.1::DECIMAL c1
       UNION ALL
       SELECT 0.1::DECIMAL c1
     ) t;
 
     # sum
     # -----
     # 0.3

By default, SQLines SQL Converter converts NUMBER to DECIMAL but if a column is the part of a primary key or unique constraint it is converted to INT by default.

You can modify the default conversion and set any other data type including DOUBLE PRECISION using -ora_number and -ora_number_int configuration options.

For more information, see Oracle to PostgreSQL Migration.