PostgreSQL – Cast String to Decimal Without Rounding the Decimal Part

postgresqlpostgresql-11type conversion

I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don't want that, I just want that it keeps all the decimals that the original table had.

Here is the query I'm using to populate this new table: 

UPDATE "Products" p
SET "conversionFactor" = (SELECT cast(a.factor_conversion as decimal)
                            FROM db2.articulo a 
                            WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)
 WHERE EXISTS(SELECT 1 
                FROM db2.articulo a
                WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)

In Products if I select that id I get: 327.58 but in the original table it is 327.580134

How can I fix that? Also other rows can have the same amount of decimals, others less, others more.

The datatypes of "conversionFactor" and factor_conversion are both strings in table articulo. I want them to be decimals in the Products table without rounding any decimals.

Best Answer

You don't show the column definition, but it must be similar to

"conversionFactor" decimal(10,2)

At any rate, the scale of the column must be 2, so PostgreSQL has to round the number to be able to sore it in that column.

You will have to use ALTER TABLE to change the colum definition to be able to store more fractional digits in the column.

Related Question