CODEWITHSUNDEEP
sqlpostgresqlaverage
TheStranger
TheStranger

Reputation: 1587

Is it possible to get up to 3 decimal places in Float in PostgreSQL?

I have a table in PostgreSQL, that have a Float column. In my select I use AVG() on that column, so often it gives a number with many decimals. Is there any way to retrict the number of decimals to a maximum of 3, meaning there can be less but not more than 3.

This is the Query:

SELECT team, AVG(score) FROM team_score_table GROUP BY team

Upvotes: 1

Views: 3901

Answers (2)

LordF
LordF

Reputation: 496

You can use a several functions to do that:

SELECT round(42.43666, 2) -- 42.44

SELECT trunc(42.43666, 2) -- 42.43

or cast:

SELECT cast(42.43666 as numeric(20, 2)) -- 42.44

according to your example should be:

SELECT team, round(AVG(score)::numeric, 2) FROM team_score_table GROUP BY team

SELECT team, trunc(AVG(score)::numeric, 2) FROM team_score_table GROUP BY team

SELECT team, cast(AVG(score) as numeric(20,2)) FROM team_score_table GROUP BY team

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can use round():

select round(val::numeric, 3)

You can also convert to a numeric, but you need a precision appropriate for your values:

select val::numeric(20, 3)

I actually prefer the explicit cast() because it sets the data type of the column to a numeric with an explicit scale -- so downstream apps are aware of the number of decimal places intended in the result.

round() returns a numeric value but it is a "generic" numeric, with no specified scale and precision.

You can see the difference in this example.

Upvotes: 1

Related Questions