Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group