On Thu, Sep 29, 2011 at 15:15, Gregor Vollmer <voll...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general