0

I have been tasked to convert some Oracle DB's to Postgresql, using AWS Schema Conversion Tool. Oracle only has the Number[(precision[,scale])] type. It would seem that Oracle stores integers using fixed-point. A lot of ID columns are defined as Number(19,0) which is being converted to Numeric(19,0) by SCT.

I've even seen one (so far) simple Number which is converted to Double Precision.

Postgres has proper scalar integer types like bigint.

On first blush it seems that storing integers as fixed-point numbers would be grossly inefficient in both storage and time compared to simple integers.

Am I missing something, does Oracle store them as efficient scalar ints under-the-covers?

Out of interest what's the best type for a simple ID column in Oracle?

David Kerr
  • 1,376
  • 2
  • 15
  • 20
  • 2
    "Oracle only has the Number[(precision[,scale])] type." is not accurate. [Oracle data types](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/Data-Types.html#GUID-219C338B-FE60-422A-B196-2F0A01CAD9A4) also shows `BINARY_FLOAT` and `BINARY_DOUBLE`. – MT0 Nov 30 '21 at 13:01
  • @MT0 I don't think it's a duplicate because this question is also asking about how to map that to a data type in Postgres. –  Nov 30 '21 at 13:10
  • @a_horse_with_no_name No, the title states "How does Oracle store integers?" and the question asked is "does Oracle store them as efficient scalar ints under-the-covers?". The OP only mentions conversion to PostgreSQL as an (irrelevant) introduction to the question. – MT0 Nov 30 '21 at 13:13

1 Answers1

1

Oracle's number data type is a variable length data type, which means that the value 1 uses less storage (and memory) than 123456789

In theory number(19,0) should be mapped to bigint, however Oracle's number(19,0) allows storing a value like 9999999999999999999 which would exceed the range for a bigint in Postgres.

The bigget value a bigint can store is 9223372036854775807 - if that is enough for you, then stick with bigint.

If you really need higher values, you will have to bite the bullet and use numeric in Postgres.

  • In hindsight I'm not really sure what I was wanting to know as the Oracle DB is no longer relevant to this project and I don't want to learn more than I have to. I will have to check, but I seriously doubt the DB would overflow a `bigint` for an ID. – David Kerr Nov 30 '21 at 14:52