search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 342 forks source link

Negative scale is unsupported in PG unlike Oracle #1595

Closed shubham-yb closed 1 year ago

shubham-yb commented 1 year ago

Hi Team, As you might know, we can provide negative scale in Oracle i.e. number(5,-2), but this is not supported in PG. When we turn on the precision using the conf parameter PG_NUMERIC_TYPE, we get the same in the exported files for negative scale which errors out.

Source:

create table num_check(n1 number(5,-2));

Exported schema:

CREATE TABLE num_check (
        n1 decimal(5,-2)
);

Corresponding error:

ERROR:  NUMERIC scale -2 must be between 0 and precision 5
LINE 2:         n1 decimal(5,-2)

Thank You for your help.

darold commented 1 year ago

Negative numeric scale are supported in PostgreSQL since version 15. If you set PG_VERSION to 14 this column will be exported with the real datatype but I recommend the use of PG 15.