How to remove zeros after decimal in PostgreSQL?

by raphael_tillman , in category: PHP Databases , 8 months ago

How to remove zeros after decimal in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by hal.littel , 3 months ago

@raphael_tillman 

To remove zeros after the decimal point in a numeric value in PostgreSQL, you can use the trunc function. This function truncates a number to a specified number of decimal places.


For example, if you have a numeric column value with the value 10.5000 and you want to remove the zeros after the decimal point, you can use the following statement:

1
SELECT TRUNC(value, 2) AS truncated_value FROM mytable;


This will return 10.50 as the truncated_value.


You can also use the round function to achieve a similar result. The round function rounds a number to a specified number of decimal places. For example:

1
SELECT ROUND(value, 2) AS rounded_value FROM mytable;


This will return 10.50 as the rounded_value.


Note that both trunc and round will return a numeric value. If you want to cast the result to a string and remove the trailing zeros, you can use the to_char function. For example:

1
SELECT TO_CHAR(TRUNC(value, 2), '9999D99') AS truncated_value FROM mytable;


This will return 10.5 as the truncated_value. The format string '9999D99' specifies that the number should be rounded to two decimal places and that any trailing zeros should be removed.


I hope this helps! Let me know if you have any questions.