Thread: Data Type for Money
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?
On Friday, December 30, 2011 9:41:18 am Carlos Mennens wrote: > 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? My guess is it is listed as numeric which is equivalent to decimal: http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Dec 30, 2011 at 12:46 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > My guess is it is listed as numeric which is equivalent to decimal: > > http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html Thanks. I just for some reason can't see or understand the difference between 'decimal' & 'numeric'. Why have two data types for the same values? Am I missing something?
Carlos Mennens <carlos.mennens@gmail.com> writes:
> Thanks. I just for some reason can't see or understand the difference
> between 'decimal' & 'numeric'. Why have two data types for the same
> values? Am I missing something?
There isn't any difference, in Postgres. There are two type names
because the SQL standard requires us to accept both names. In a quick
look in the standard it appears that the only difference is this:
17)NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18)DECIMAL specifies the data type exact numeric, with the decimal
scale specified by the <scale> and the implementation-defined
decimal precision equal to or greater than the value of the
specified <precision>.
ie, for DECIMAL the implementation is allowed to allow more digits than
requested to the left of the decimal point. Postgres doesn't exercise
that freedom so there's no difference between these types for us.
regards, tom lane
From: http://msdn.microsoft.com/en-us/library/windows/desktop/ms710150(v=vs.85).aspx ODBC type SQL_DECIMAL maps to SQL type DECIMAL(p,s) Signed, exact, numeric value with a precision of at least p and scale s. (The maximum precision is driver-defined.) (1 <=p <= 15; s <= p).[4] ODBC type SQL_NUMERIC maps to SQL type NUMERIC(p,s) Signed, exact, numeric value with a precision p and scale s (1 <= p <= 15; s <= p).[4] Footnote [4]: [4] SQL_DECIMAL and SQL_NUMERIC data types differ only in their precision. The precision of a DECIMAL(p,s) is an implementation-defineddecimal precision that is no less than p, whereas the precision of a NUMERIC(p,s) is exactly equalto p. For ODBC, numeric values are stored in a structure of type SQL_NUMERIC_STRUCT: struct tagSQL_NUMERIC_STRUCT { SQLCHAR precision; SQLSCHAR scale; SQLCHAR sign[g]; SQLCHAR val[SQL_MAX_NUMERIC_LEN];[e], [f] } SQL_NUMERIC_STRUCT; If you examine the contents of the ODBC include file sqltypes.h you will see how the values are stored internally for ODBCtransmission of the data from SQL to C. The major difference between the two types is that DECIMAL is of arbitrary precision, defined by the driver vendor, and mustcontain at least 15 digits of precision in maximum precision columns, but could contain more significant digits up toa driver specified maximum. So DECIMAL may possibly hold more digits than NUMERIC can. In the case of PostgreSQL, the number of possible significantdigits for decimal is immense (1000 digits, IIRC). More than anyone other than a theoretical mathematician wouldever need. I believe in the case of PostgreSQL, when you declare a column of type NUMERIC, you will actually create a DECIMAL column. So for all practical purposes they are synonyms in PostgreSQL. If you bind as an ODBC type, you will live underthe limitations of ODBC binding. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Carlos Mennens Sent: Friday, December 30, 2011 10:10 AM To: PostgreSQL Subject: Re: [GENERAL] Data Type for Money On Fri, Dec 30, 2011 at 12:46 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > My guess is it is listed as numeric which is equivalent to decimal: > > http://www.postgresql.org/docs/9.1/interactive/datatype-numeric.html Thanks. I just for some reason can't see or understand the difference between 'decimal' & 'numeric'. Why have two data typesfor the same values? Am I missing something? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general