How to format numbers as currency in Postgres, MySQL and Redshift

The all-important revenue graph

In your venerable orders table, you’re almost certainly storing prices as numbers. Perhaps they’re integer, perhaps they’re numeric, perhaps you’re using Postgres and they’re money, or perhaps you rolled the dice on floating-point rounding errors and went with real.

But save for Postgres’s money format, your revenue graph looks, well, not like revenue at all:

Wouldn’t you rather look at this?

That’s a revenue graph we can all get behind!

Formatting the query

Our query starts like this:

select date(created_at), sum(price)
from orders
group by 1

Let’s rewrite it to get some nice currency formatting.

Postgres

Assuming you’re not already using the money type, you can leverage it for some quick formatting:

select date(created_at), cast(sum(price) as money)
from orders
group by 1

MySQL

Things in MySQL aren’t quite so easy. But we can still format the number to get two decimal places and prepend a “$”:

select date(created_at), concat('$', format(sum(price), 2))
from orders
group by 1

Redshift

Unfortunately Redshift doesn’t support the money type or the format function. We’ll use the to_char function to specify an exact format string:

select 
  date(created_at), 
  to_char(sum(price), 'FM$999,999,999,990D00')
from orders
group by 1

Our format string covers a few bases:

• FM removes leading and trailing whitespace; this allows our “$” to be right next to the number
• $ is our dollar sign
• The 9s are optional digits
• The commas separate the thousands, millions, etc.
• The 0s are required digits; all numbers will have a ones place and two decimal places, with zeroes in those places if necessary
• The D specifies our decimal “.”
• This SQL works on all 3 of these databases, but it’s a bit onerous to type, so we prefer the other options on Postgres and MySQL

Of course, using a tool like Sisense for Cloud Data Teams, you can reduce all of this to a single click:

Or if you prefer typing to clicking, you can annotate the SQL itself!