Thread: int8 version of NUMERIC?
In Interbase and Firebird, NUMERIC is implemented as 64-bit integer. This limits the range to NUMERIC(18, *) but for many uses that's adequate. And moreover it's fast and efficient. Is there a way in PostgreSQL to do something similar, i.e. I want to: - use 64-bit ints, not string bits or arbitrary precision which is dubbed as "much slower than ints" in the documentation; - use decimals, like NUMERIC(18,4); - store and retrieve decimal numbers pretty much transparently (e.g. I don't want to remember to insert 123456 for 12.3456 and playing with multiplying/dividing by 10000); -- dave
David Garamond wrote: > In Interbase and Firebird, NUMERIC is implemented as 64-bit integer. > This limits the range to NUMERIC(18, *) but for many uses that's > adequate. And moreover it's fast and efficient. > > Is there a way in PostgreSQL to do something similar, i.e. I want to: > > - use 64-bit ints, not string bits or arbitrary precision which is > dubbed as "much slower than ints" in the documentation; They call that a 'bigint', which is in SQL-spec and I noticed it got inserted in firebird 1.5 > - use decimals, like NUMERIC(18,4); Well, decimal and numeric are both available in postgres > - store and retrieve decimal numbers pretty much transparently (e.g. I > don't want to remember to insert 123456 for 12.3456 and playing with > multiplying/dividing by 10000); You can just use 12.3456 in postgres. Best regards, Arjen
Arjen van der Meijden wrote: >> In Interbase and Firebird, NUMERIC is implemented as 64-bit integer. >> This limits the range to NUMERIC(18, *) but for many uses that's >> adequate. And moreover it's fast and efficient. >> >> Is there a way in PostgreSQL to do something similar, i.e. I want to: >> >> - use 64-bit ints, not string bits or arbitrary precision which is >> dubbed as "much slower than ints" in the documentation; > > They call that a 'bigint', which is in SQL-spec and I noticed it got > inserted in firebird 1.5 BIGINT has been part of IB/FB since IB version 6.0 (1999-2000). >> - use decimals, like NUMERIC(18,4); > Well, decimal and numeric are both available in postgres >> - store and retrieve decimal numbers pretty much transparently (e.g. I >> don't want to remember to insert 123456 for 12.3456 and playing with >> multiplying/dividing by 10000); > You can just use 12.3456 in postgres. Perhaps I wasn't clear enough. What I meant to say was, IB/FB uses BIGINT internally for NUMERIC & DECIMAL. Thus, they don't actually have arbitrary precision, they can only support NUMERIC(18, X) or DECIMAL(18, x). This is probably adequate for most uses. Plus they are fast, since they use native integers just like BIGINT. My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very slow compared to INT/BIGINT. Should I worry about that? -- dave
On Sun, Jan 11, 2004 at 21:53:09 +0700, David Garamond <lists@zara.6.isreserved.com> wrote: > > My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very > slow compared to INT/BIGINT. Should I worry about that? Most likely disk IO not cpu will be your bottleneck and the extra overhead of numeric relative to int or float won't be a big deal. Numeric is stored usingh based 10000 (at least in 7.4.x) and hence isn't that horrible performance-wise (as compared to say storing it as an ascii string).
On 12/1/04 3:28 pm, "Bruno Wolff III" <bruno@wolff.to> wrote: > On Sun, Jan 11, 2004 at 21:53:09 +0700, > David Garamond <lists@zara.6.isreserved.com> wrote: >> >> My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very >> slow compared to INT/BIGINT. Should I worry about that? > > Most likely disk IO not cpu will be your bottleneck and the extra overhead > of numeric relative to int or float won't be a big deal. > > Numeric is stored usingh based 10000 (at least in 7.4.x) and hence isn't > that horrible performance-wise (as compared to say storing it as an ascii > string). Out of interest, where does the performance of storing at as TEXT suffer here... Reading or writing or both? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Adam Witney wrote: > On 12/1/04 3:28 pm, "Bruno Wolff III" <bruno@wolff.to> wrote: > >> On Sun, Jan 11, 2004 at 21:53:09 +0700, >> David Garamond <lists@zara.6.isreserved.com> wrote: >>> >>> My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very >>> slow compared to INT/BIGINT. Should I worry about that? >> >> Most likely disk IO not cpu will be your bottleneck and the extra overhead >> of numeric relative to int or float won't be a big deal. >> >> Numeric is stored usingh based 10000 (at least in 7.4.x) and hence isn't >> that horrible performance-wise (as compared to say storing it as an ascii >> string). > > Out of interest, where does the performance of storing at as TEXT suffer > here... Reading or writing or both? It starts suffering when you start "doing" something with the data, like asking for the sum() or the avg(). But exactly at that time arbitrary precision is IMHO needed, because we don't give the closest possible approximation like MySQL, we give you a result. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #