PostgreSQL format decimal separator from point to comma

Introduction

One of my colleges asked me to export a lot of data for him so he could use it in Excel. Decimal numbers in the Postgres database are - as everywhere in information technology or programming - separated by a dot/point.

In Germany the default separator is the comma, so Excel “misinterprets” the data and creates wrong values. There is no sense in changing the representation in the database.

Solution

While selecting the data for the export, use TO_CHAR for transforming the representation of dates and the replace for replacing the decimal separator respecting to your country.

SELECT
  TO_CHAR(ob.someObject_date, 'YYYY-MM-DD') AS someObjectDate,
  ob.someObject_status,
  replace(ob.commission::text, '.', ',') AS commission,
  replace(ob.amount::text, '.', ',') AS amount
FROM someObject AS ob
ORDER BY ob.someObject_date;