CODEWITHSUNDEEP
sqlpostgresqlformatting
psilocybin
psilocybin

Reputation: 1115

PostgreSQL: CAST() as money: specify currency

I'm looking to specify the currency as GBP when casting as money in PSQL. Currently, this prefixes the formatted value as USD (with $):

SELECT CAST(SUM(cost) AS MONEY) AS Total FROM orders;

Gives:

Total | $13,266,314.00

Is there a nice way way to specify £ when casting to money instead of $ or will this require CONCAT(), ROUND() and TO_CHAR()?

Upvotes: 7

Views: 11303

Answers (2)

Connor Leech
Connor Leech

Reputation: 18833

You need to set your currency locale to be pounds instead of dollars. You can do this by setting a value for lc_monetary.

SET lc_monetary = 'en_GB';
SELECT CAST(SUM(cost) AS MONEY) AS Total FROM orders;

As mentioned in the locale docs you can get a list of available locales on a Unix system by running locale -a.

Upvotes: 3

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You may set lc_monetary in postgresql.conf, or within your sql client. Here is the latter test case:

set lc_monetary to "en_IE.utf8";
select 10::money;

 money  
--------
 €10.00
(1 row)

Upvotes: 8

Related Questions