ORACLE NUMBER(*,0) equivalent in POSTGRESQL

0
votes

I have a table in ORACLE with a column which has a data type of NUMBER(*,0). As per my understanding it means precision will be 38 since that is the maximum limit of precision and scale value will be 0 which means no numbers will be allowed after decimal point i.e. will store integers?

I have a requirement where I have to create same table in PostgreSQL. Is it possible to write NUMERIC(*,0) in PostgreSQL? If not, what will be PostgreSQL equivalent of NUMBER(*,0)?

2
sqlpostgresqloracledatabase-migrationsqldatatypes
postgresql.org/docs/9.5/datatype-numeric.html – James
I would use numeric with no precision – Philippe
If i just write NUMERIC wont it create with any precision and scale? I want a scale of 0 @Philippe – Hetal Rachh
You should investigate what you are storing in that column. Maybe a bigint in Postgres is enough? – a_horse_with_no_name

2 Answers

1
votes

in oracle numeric (precision,scale)

number(precision,scale)
numeric (precision,scale)

precision = If a precision is not specified, the column stores values as given

scale = If no scale is specified, the scale is zero

oracle number

in sql,postgresql

numeric(precision,scale)

precision = With a maximum length of 38 and With default value 18 in sql

1
votes

Postgres supports arbitrary precision numeric values. The Oracle number type has up to 38 digits of precision. So, the type:

number(*, 0)

is really:

number(38, 0)

The equivalent in Postgres is:

numeric(38)

or:

numeric(38, 0)

However, Postgres supports ridiculously large precisions and scales. So if you specify: numeric

You will be able to represent any number that you are likely to encounter or need in your lifetime.