Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: 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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    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)

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

    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.

    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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •