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?
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.