probable faq: internal format of numerics

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: probable faq: internal format of numerics
Date: 2006-04-25 14:58:10
Message-ID: 444E3902.7010806@janestcapital.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

This is probably a FAQ, but I've googled the question and haven't found
an answer. What is the internal format of numerics? I have a
situation where I'm dealing with monetary values (so I want to use
numerics), but I can probably put fairly hard limits on how many digits
of precision I need. So I'm wondering where the stepping points are-
the points were adding another digit of precision increases the size of
the row. I can think of three different ways numerics could be stored
in PostGreSQL:

1) fixed precision integer format. Numeric(a,b) is stored as an n-bit
signed binary number (where n is large enough that 2^(n-1) > 10^a), and
a integer value i represents the value i * 10^(-b). This means a
numeric(18,4) takes up two 32-bit word, while a numeric(19,4) takes up
three (or four?) 32-bit words, and the number 12345 represents the value
1.2345.

2) nibble-based BCD Every digit is stored (in BCD form) in a 4-bit
nibble, so two digits fit in a byte. Here, a numeric(16,4) takes up 64
bits of space, while a numeric(17,4) takes up 68 (aka 96 or 128) bits of
space, and the value 1.2345 is stored as the hexadecimal number 0x12345.

3) byte-based BCD. Same as above, except that only one digit is stored
per byte, not two. Generally, you're storing the ASCII text strings if
you're doing this, so that the value 1.2345 is stored as the hexadecimal
string 0x31 0x32 0x33 0x34 0x35, and a numeric(16,4) takes up 16 bytes,
while a numeric(17,4) takes up 17 (20? 24?) bytes.

I'm hoping that #1 will be the correct answer, but I haven't been able
to find out yet. Sorry for asking a faq.

Brian

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-04-25 15:40:52 Re: probable faq: internal format of numerics
Previous Message Andrew Chambers 2006-04-24 20:45:44 Re: stress tests problems