Recommended data type for storing currency values

ablack3

In the Vertica database I'm using currency values are stored as text and implicitly cast to numeric type any time a numeric calculation is done. This seems to be bad practice to me, but I don't have much database experience. The argument for doing this is that storing currency values as text facilitates exports from the database. Does this make sense? What datatype would be recommended as best practice for storing currency values?

Jim_Knicely

Hi,

I think it is much better to store currency using a NUMERIC vs. a VARCHAR. Why?

Say I have the following two tables that store a currency value. One uses a VARCHAR column and the other a NUMERIC column:

dbadmin=> \d currency_varchar
                                          List of Fields by Tables
 Schema |      Table       |  Column  |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------------+----------+-------------+------+---------+----------+-------------+-------------
 public | currency_varchar | currency | varchar(12) |   12 |         | f        | f           |
(1 row)

dbadmin=> select * from currency_varchar;
   currency
--------------
 123456789.12
(1 row)

dbadmin=> \d currency_numeric
                                           List of Fields by Tables
 Schema |      Table       |  Column  |     Type      | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------------+----------+---------------+------+---------+----------+-------------+-------------
 public | currency_numeric | currency | numeric(11,2) |    8 |         | f        | f           |
(1 row)

dbadmin=> select * from currency_numeric;
   currency
--------------
 123456789.12
(1 row)

Although each table consumes that same amount of license (raw storage), notice that NUMERIC data type compresses better on disk:

RAW:

dbadmin=> select audit('currency_varchar');
 audit
-------
 12
(1 row)

dbadmin=> select audit('currency_numeric');
 audit
-------
 12
(1 row)

COMPRESSED:

dbadmin=> select projection_name, row_count, used_bytes from projection_storage where projection_id in (select projection_id from projections where anchor_table_name in ('currency_varchar', 'currency_numeric'));
    projection_name     | row_count | used_bytes
------------------------+-----------+------------
 currency_varchar_super |         1 |         85
 currency_numeric_super |         1 |         55
(2 rows) 

And the worst part of using a VARCHAR in this case is the HUGE hit on performance because of the data type conversion...

dbadmin=> insert into currency_varchar select currency from currency_varchar cross join tables cross join vs_columns;
 OUTPUT
---------
 6520500
(1 row)

dbadmin=> insert into currency_numeric select currency from currency_numeric cross join tables cross join vs_columns;
 OUTPUT
---------
 6520500
(1 row)

dbadmin=> \timing
Timing is on.

dbadmin=> select count(*) from currency_varchar where currency = 123456789.12;
  count
---------
 6520501
(1 row)

Time: First fetch (1 row): 1452.319 ms. All rows formatted: 1452.405 ms

dbadmin=> select count(*) from currency_numeric where currency = 123456789.12;
  count
---------
 6520501
(1 row)

Time: First fetch (1 row): 76.595 ms. All rows formatted: 76.674 ms

Notice that the second query (no data type conversion) is 19x faster than the first query (requires data type conversion)!

marcothesane

First - I fully agree with Jim. We calculate with currency values (sum them, get their average, run linear regressions with them, etc). Therefore they need to be numeric.

There could be use cases where you'd be better of with FLOATs , for example, when you need to perform exponential calculations with them - like, for example projecting the value of an old age savings scheme, which bases itself on yearly instalments and the effect of interest on interest.

On the other hand, certain insurance companies have agreed to store co-efficients and the money amounts that they are applied to with a common precision, and always to round them to that precision in calculations. This screams for the NUMERIC(n,m) type, as CAST-ing to a certain numeric precision, in Vertica, results in automatic rounding instead of truncation:

    SQL>select 3.225::NUMERIC(4,2), 3.224::NUMERIC(4,2)
    ?column?|?column?
        3.23|    3.22

And I might want to add: if a precision of 18 digits overall is sufficient, don't go above it; the value will be able to be treated in many situations like a 8-byte/ 64bit integer - the fastest type in Vertica. From 19 to 36 digits, it's 16 bytes - and with that, always two CPU cycles for comparison and simple calculations instead of just 1 with 64 bits.

Personally, if I get no guidance from a customer, I usually shape currency values as NUMERIC(18,4); many commercial calculations I have come across store intermediate results with a precision of 4 digits behind the decimal point, before rounding to just 2 after the decimal point, or, in the case of Switzerland, to 5 Franc Cents.

Happy playing -
Marco