This page in other versions: 9.1 / 9.2 / 9.3 / 9.4 / current (9.5)  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0

8.2. Monetary Types

The money type stores a currency amount with a fixed fractional precision; see Table 8-3. Input is accepted in a variety of formats, including integer and floating-point literals, as well as "typical" currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale. Non-quoted numeric values can be converted to money by casting the numeric value to text and then money:

SELECT 1234::text::money;

There is no simple way of doing the reverse in a locale-independent manner, namely casting a money value to a numeric type. If you know the currency symbol and thousands separator you can use regexp_replace():

SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;

Since the output of this data type is locale-sensitive, it may not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump make sure lc_monetary has the same or equivalent value as in the database that was dumped.

Table 8-3. Monetary Types

Name Storage Size Description Range
money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07


Dec. 27, 2008, 9:08 p.m.

Be very careful when using the money datatype. It doesn't allow many numeric operators. It's not possible to do something like SELECT '1.0'::money/'2.0'::money. Instead use numeric to store monetary data.

Here's a quick way to convert (converting "PriceList"."Price" (money) to "PriceList"."PriceNumeric" (numeric) in this case):
UPDATE "PriceList"
SET "PriceNumeric"=regexp_replace("Price"::varchar,E'(\\$|,)','','g')::numeric

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group