"money" binary representation

Konstantin Izmailov wrote:

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7).
The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h,
for the value '$50.2'. I could not find description anywhere on how to
convert the binary data into, for example, a double precision number.

Would you please help me find a method of converting binary "money"
data into a double precision?

Its my understanding that MONEY is deprecated that you really should
store money values as NUMERIC instead.

a wild guess says thats some variant on NUMERIC, which is stored in
base 10000 as a series of 16 bit integers, with a fuixed point fraction.

why would you convert money to floating point? $0.10 in binary floating
point is a repeating fraction which can't be represented exactly

btw, are you sure your value isn't $51.20 ? 0x1400 is 5120 decimal.

Konstantin Izmailov <pgfizm@gmail.com> writes:

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
value '$50.2'. I could not find description anywhere on how to convert the
binary data into, for example, a double precision number.

Would you please help me find a method of converting binary "money" data
into a double precision?

It's a binary integer, either int4 or int8 depending on which PG version
you're working with, measured in pennies (or whatever the minimum unit
of your currency is). So that should correspond to 5020.

regards, tom lane

Right, the value is '$51.20'! Now I understand how to interpret the bytes -
thank you!

I had to work with an existing database and I do not know why they still use
"money" fields.
On Sun, Nov 15, 2009 at 9:38 PM, John R Pierce <pierce@hogranch.com> wrote:

On 2009-11-16, Konstantin Izmailov <pgfizm@gmail.com> wrote:

--000e0cd5d09230ff7d04787526aa
Content-Type: text/plain; charset=ISO-8859-1

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
value '$50.2'. I could not find description anywhere on how to convert the
binary data into, for example, a double precision number.

money is a 64 bit integer representing a number of cents, it appears
to be big-endian byte order on ypur system

it is deprecated and should not be used in new applications, use some
sort of numeric instead.

Would you please help me find a method of converting binary "money" data
into a double precision?

floating point is not recomended for financial calculations, but
dividing the integer value by 100.0 should get you there.

On Mon, Nov 16, 2009 at 3:02 AM, Jasen Betts <jasen@xnet.co.nz> wrote:

On 2009-11-16, Konstantin Izmailov <pgfizm@gmail.com> wrote:

--000e0cd5d09230ff7d04787526aa
Content-Type: text/plain; charset=ISO-8859-1

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
value '$50.2'. I could not find description anywhere on how to convert the
binary data into, for example, a double precision number.

money is a 64 bit integer representing a number of cents, it appears
to be big-endian byte order on ypur system

it is deprecated and should not be used in new applications, use some
sort of numeric instead.

Would you please help me find a method of converting binary "money" data
into a double precision?

floating point is not recomended for financial calculations, but
dividing the integer value by 100.0 should get you there.

if you are reading/writing binary data to/from postgres, you should
consider using libpqtypes:

PGmoney m;
res = PQexecf(conn, "INSERT INTO foo(m) values(%money) returning m", m);
PQgetf(res, 0, "#money", "m", &m); /* # notation pulls field by name */

boy, this question sure seems to come up an awful lot!

merlin

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the

Huh... You mean 8 bytes, right?

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/