Re: Data Type for Money

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-defined decimal precision that is no less than p, whereas the precision of a NUMERIC(p,s) is exactly equal to 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 ODBC transmission 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 must contain at least 15 digits of precision in maximum precision columns, but could contain more significant digits up to a driver specified maximum.

So DECIMAL may possibly hold more digits than NUMERIC can. In the case of PostgreSQL, the number of possible significant digits for decimal is immense (1000 digits, IIRC). More than anyone other than a theoretical mathematician would ever 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 under the limitations of ODBC binding.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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(dot)klaver(at)gmail(dot)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?

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general