Re: 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(dot)klaver(at)gmail(dot)com

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?

Carlos Mennens <carlos(dot)mennens(at)gmail(dot)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-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