How do I load decimal numbers into a decimal field in Postgres?

I have incoming data that includes a field has decimal numbers. It has long-precision numbers as well as integers like the following:

6.6667

8.3333

The target schema is a Postgres database and I set the field type as Decimal(3,2) as I only really need up to the tenths place.

So I've tried casting this field within Alteryx to Float, Double, FixedDecimal, and Double:Forced with no luck. How do I clean this field so that it loads into a Decimal(3,2) field in Postgres?

Developer

Accepted answers

GBO95

@bkimup

Actually, setting the size to 3.2 will cause error because the size is not enough for your data.

The 3 in the "3.2" refers to the number of characters. The "2" after the "." is the number of decimal places.

In your use case, you will need to set the size to 5.2.

Since the largest number is 10 (with 2 decimal places), the end result will be "10.00". So there will be 5 characters including the "."

If you expect larger numbers in the future, I would recommend setting the number before the "." to a large value while keeping the number after the "." at 2.

Hope this helps!

Ganesh

All comments

Qiu

@bkimup
Can you give a sample to show us what result you want?

JBLove

Based on your Postgre SQL comment, it sounds like your goal is for the data to appear as X.XX.

Excluding your "10" example, that should be achievable with FixedDecimal(4.2), although I would suggest FixedDecimal(5.2) in case negatives appear in your expanded dataset. In Alteryx, the decimals and negative signs are part of the precision.

I hope this helps. If this is not what you need, as @Qiu mentioned provide examples of how you expect the data to appear.

Himanshu_1984

Hi bkimup,

The maximum value for decimal(3, 2) is 9.99, and in your case you have max value is 10 hence you can change the data type up-to 4,2 by using select tool or use formula tool. Hope that helps

image.png

GBO95

@bkimup

Actually, setting the size to 3.2 will cause error because the size is not enough for your data.

The 3 in the "3.2" refers to the number of characters. The "2" after the "." is the number of decimal places.

In your use case, you will need to set the size to 5.2.

Since the largest number is 10 (with 2 decimal places), the end result will be "10.00". So there will be 5 characters including the "."

If you expect larger numbers in the future, I would recommend setting the number before the "." to a large value while keeping the number after the "." at 2.

Hope this helps!

Ganesh

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703