Numeric Overflow while converting oracle to PostgreSQL

Question

I am converting a very old Oracle DB to Postgres where most of the precision of the numeric columns are missing, however, In Postgres table structure the precision to the same columns are present, I am getting a plethora of numeric overflow error while moving data from Oracle DB to Postgres.

Now, I want to update all those out of precision values to the largest number of the precision in the oracle database, so, I am trying to create an update script using a Postgres table structure that can help me to find all the damaged rows in the oracle.

My query is as below(to be run on Postgres), that I want to modify to get the update script which I can run on oracle DB to get the corrupt values.

select 'update ' ||table_name || 'set ' || column_name || '=' || (numeric_precision - numeric_scale) || ' where column_name > ' || (numeric_precision - numeric_scale) from information_schema.columns where table_schema='test' and data_type='numeric';

Now here (numeric_precision - numeric_scale) if it returns a value 3 then I want to replace it with 999 in case if it is 4 then I want it to be 9999.

Please can someone let me know how this is possible, I want to run this query on Postgres DB and then I will run the resulting script on the Oracle DB?

ScaisEdge · Accepted Answer · 2020-07-12 09:24:32Z

2

You could use the length of your (numeric_precision - numeric_scale) result for add the pattern you prefer using LPAD

LPAD('', length((numeric_precision - numeric_scale)), '9' )

answered Jul 12, 2020 at 9:24

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sujeet Chaurasia Over a year ago

Thanks, it worked, only I have removed the length function,LPAD('', (numeric_precision - numeric_scale), '9' )

Collectives™ on Stack Overflow

Numeric Overflow while converting oracle to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related