I am moving a data from oracle table to the Postgres using with help of dblink, and in the new table structure of the Postgres I have added precision to the numeric column which were without precision in Oracle, now while moving the data I am able to truncate the decimal values to match the scale, however, I am struggling to find a way to truncate a value before the decimal point if it does not match the precision.
to adjust the scale I am using the below command:
insert into Postgres.test
select id, width::numeric(7,3)
from oracle.test;
So, in this case, if the value after the decimal is greater than 3 digits it will truncate it to the 3 digits, however, I want if the precision (before the decimal) is greater than 4 than it should also get truncated to the 4 digits, I don't mind if the value of few rows gets changed by doing so.
You can “left truncate” the number before the decimal point with
insert into Postgres.test
select id, (width % 10000)::numeric(7,3)
from oracle.test;
But I don't see much point in doing that. It might be better to use a NULL than a bad value:
insert into Postgres.test
select id, CASE WHEN width >= 10000 THEN NULL ELSE width::numeric(7,3) END
from oracle.test;