The field that I am using comes in as type: text and I want to convert it to a number with 2 decimal places. For some reason the following convert it to a number but also rounds it:

"UNIT_PRICE"::numeric

"UNIT_PRICE"::decimal

both convert a 0.65000000000000 text value to 1 but I would like to get a numeric 0.65

Is it possible?

CodePudding user response：

In Redshift the default precision and scale are 18 and 0 - DECIMAL(18,0). This is whole numbers. If you want a different scale you need to specify it. DECIMAL(18,2).

See: Numeric types - Amazon Redshift

CodePudding user response：

You can write a cast function with as a decimal to get only 2 decimals back.

SELECT CAST(0.65000000000000 AS DECIMAL(10, 2))

More information about casting as decimal can be found here: SQL Server CAST() Function