Hi, A very naive question. I need to store currency in the database. Both the value and the code. To solve this, do people generally make 2 columns, one storing the value and other the code? Or is there an inbuilt type I can use?
-thanks
A:
The relational databases I know (Oracle, Postgres, MySQL) do not have built-in currency support, and I don't expect any others do. You will have to do it yourself with 2 columns.
FelixM
2009-12-04 01:14:27
postgresql does have a 'money' type - http://www.postgresql.org/docs/8.3/static/datatype-money.html
warren
2009-12-04 01:22:15
It does indeed have that type, but I don't see how you can specify the currency itself; the documentation just mentions locale-specific display.
FelixM
2009-12-04 18:04:32
A:
There may be tools specific to your db. In general I would think storing the currency as a real value and a currency code indicator. There may be a standardized currency code list somewhere that you can use or you can just make one up based on the currencies you know you will deal with.
Really if you want to indicate a type of any sort you will need a column to store the type.
People might be able to provide more DB specific help if you indicate the DB you are using.
Arthur Thomas
2009-12-04 01:14:43
http://en.wikipedia.org/wiki/ISO_4217 is an excellent standardized currency code list.
Jeff Sternal
2009-12-04 01:20:27
+1
A:
Some databases have a Money type, which can be used to store the value. However, if you want to store a code (do you mean dollars, euro's etc?) then you would need to use a second column.
Update: PostgreSQL does have a money type, although it looks like it would only support one type of currency so it still does not really meet your needs.
Justin Ethier
2009-12-04 01:15:49
Yeah, I use postgresql but I use numeric to store money since I want to use 3 decimal places. Just handling it yourself gives the most options.
Arthur Thomas
2009-12-04 01:23:29
+3
A:
You will need to use two columns. I would store the monetary amount in one column and the alpha currency code in another column. In some cases, you will have multiple amounts on a single row. e.g. shipping amount and tax amount may both be on the invoice record. You will need to decide if these will share the same currency or if you need two columns.
You should use the ISO standard currency codes.
WW
2009-12-04 01:19:20