On Thu, Sep 29, 2011 at 15:15, Gregor Vollmer
<vollmer@ekp.uni-karlsruhe.de> wrote:
> We do not do any arithmetic on the columns, only saving and retrieval, is
> decimal as fast as bigint in that case?
It's slightly slower because numeric is passed around by pointer, not
by value. Bigint is by-value in 64-bit Postgres versions. But that's
probably only noticeable if you're processing lots of values in a
single query, or running CREATE INDEX.
Personally I'd choose bigint for efficiency reasons. But always keep
in mind that arithmetic works differently on integers and numerics:
db=# select 1::bigint/10 as x;
x
---
0
db=# select 1::numeric/10 as x;
x
------------------------
0.10000000000000000000
> How does decimal store the number internally, is it a fixed size through-out
> all rows and how does it compare to bigint?
Numeric (decimal) is always variable-length. The specification in
column type doesn't affect storage. Bigint is always 8 bytes.
For numbers with less than 8 digits, numeric is slightly smaller than
bigint. For larger numbers, bigint is smaller.
create table dec (i numeric);
insert into dec values(0), (1), (11), (101), (1001), (10001),
(100001), (1000001), (10000001), (100000001), (1000000001),
(10000000001), (100000000001), (1000000000001);
select pg_column_size(i), i::text from dec;
pg_column_size | i
----------------+---------------
3 | 0
5 | 1
5 | 11
5 | 101
5 | 1001
7 | 10001
7 | 100001
7 | 1000001
7 | 10000001
9 | 100000001
9 | 1000000001
9 | 10000000001
9 | 100000000001
11 | 1000000000001
Regards,
Marti