Which data type to use while creating table when column is a mix of whole numbers as well as decimals?

Question

I am creating a table in Redshift (based on PostgreSQL), a few columns of that table's source data are a mix of whole numbers as well as decimals.

If I set decimal(5,5) it throws error for integers, if I set integer it throws error for decimals, which type fits both?

Answer 1

How many digits of your integers do you need to reasonably be able to store? Call that number i.

How many digits after the decimal do you need for your decimals? Call that number d.

You now know that you want a decimal(i+d,d). But you need to check whether such a combination is valid in the documentation. If it is, great. If not, you need to trade off some digits to arrive at something acceptable.