Use PostgreSQL REPLACE() to replace dots with commas (dollar to euro)

If you have set up your database tables correctly you might be using double-precision floating numbers to store currency values. This works great because dollars use dots to represent decimals.

The problem starts when it’s not actually dollars you are storing but euros, and maybe you need to copy query output to Excel or LibreOffice Calc to work with these Euro values.

Both of these spreadsheet programs don’t know how to correctly handle the dots or how to correctly import them– at least without some tricks. There are different ways to deal with this, but this is all after you copied the data over to your spreadsheet. Find and replace is a common one.

But I like to start at the source. (Yes, you can change your system locale and all that, but I would advise against that for other reasons).

So assuming this is a query you would like to run regularly, instead of running this (which will give you the dotted price):

SELECT product, 
price as price_with_dot
FROM products

You can use REPLACE(), to replace the dot with commas and cast the double-precision float to text.

SELECT product, 
REPLACE(ROUND(price),2)::text, '.', ',') as "price_with_comma"
FROM products

For good measure, I also use ROUND() to round to two decimals.