CODEWITHSUNDEEP
postgresqloracle-fdw
Sujeet Chaurasia
Sujeet Chaurasia

Reputation: 163

Truncating a value before a decimal in PostgreSQL

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.

Upvotes: 0

Views: 868

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246898

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;

Upvotes: 1

Related Questions