If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > Data Type For Money / Currency

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-30-11, 13:05
CarlosinFL CarlosinFL is offline
Registered User
 
Join Date: Oct 2010
Location: Orlando, FL
Posts: 184
Question Data Type For Money / Currency

I'm trying to understand what is the recommended data type for $ in
PostgreSQL. I've done some research and from what I've gathered, there
are a few options:

1. decimal
2. money

I've read the 'money' data type is non-standard and I should avoid
using this. I see it a bunch of Microsoft SQL Server which I assume
works great but I'm using PostgreSQL and want to make sure I'm ANSI
SQL compliant. I would normally just use 'decimal' however when I'm in
doubt, I use pgAdmin3 as a cheat sheet and upon building a new column,
under 'data type', there is no option for decimal but there is for
money. I'm very confused as I assumed 'money' was a non-standard
option for SQL Server and 'decimal' was the correct value but it's not
an option in the pgAdmin3 GUI.

Any tips and or advice?
Reply With Quote
  #2 (permalink)  
Old 12-30-11, 13:19
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 2,408
The money datatype in PostgreSQL is "somewhat" deprecated and has a lot of issues (e.g. not able to handle currencies that deviate from the locale). There have been several discussions on the mailing list if that should actually be removed completely in a future version.

To store accurate values you should use the numeric (or decimal) datatype. Doing so will save you a lot of trouble in the long run

I don't use pgAdmin so I can't comment on that. Does it offer the numeric datatype? It is equivalent to decimal.

But you should create your tables by through SQL scripts anyway - not through some GUI tool. Ideally those script are also stored in a version control system.
Again that will save you some headaches in the long run.
Reply With Quote
  #3 (permalink)  
Old 12-30-11, 13:32
CarlosinFL CarlosinFL is offline
Registered User
 
Join Date: Oct 2010
Location: Orlando, FL
Posts: 184
OK there's a value for 'numeric' which from what I can see is identical / same thing as 'decimal'. Why is there two different data types which have the same value? I can't find a single slight difference between 'decimal' & 'numeric' except that 'numeric' is a data type option available in pgAdmin3.

I don't know what SQL scripts you're referring to when I create tables but I don't use a GUI / pgAdmin3 to generate tables. I do them all by hand using my psql client. I only refer to pdAdmin3 when I can't remember something or need help. I do what I'm trying to create in psql but before I submit, I simply look at the SQL window and learn from there. Even then I manually type the SQL commands from pgAdmin3 into my psql client. Helps me learn.

So if I'm creating a field called 'salary' with a data type of 'numeric', how exactly do I code this? I know there's a 'precision' & 'scale' but I can't find any examples of how to create one. Lets say I have a field / column for salary and I want to input a users anual salary of $32,412, would that be a scale 2 and precision 3?

salary numeric(2,3)
Reply With Quote
  #4 (permalink)  
Old 12-30-11, 14:25
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 2,408
Quote:
Originally Posted by CarlosinFL View Post
OK there's a value for 'numeric' which from what I can see is identical / same thing as 'decimal'. Why is there two different data types which have the same value? I can't find a single slight difference between 'decimal' & 'numeric' except that 'numeric' is a data type option available in pgAdmin3.
As I said they are equivalent. Why that is so I don't know. You would need to ask that on the pg mailing list.

Quote:
I don't know what SQL scripts you're referring to
Any DBMS schema should be created by using a SQL script with the approriate CREATE TABLE statements. Nothing should be done "ad-hoc" in psql or any other SQL client.

Quote:
I only refer to pdAdmin3 when I can't remember something or need help
You should rather consult the manual. Everything (including numeric and decimal) is documented there: PostgreSQL: Documentation: Manuals: Data Types

Quote:
I know there's a 'precision' & 'scale' but I can't find any examples of how to create one. Lets say I have a field / column for salary and I want to input a users anual salary of $32,412, would that be a scale 2 and precision 3?
Again, this is documented in the manual:

PostgreSQL: Documentation: Manuals: Numeric Types

Quote:
Originally Posted by The Fine Manual
The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On