By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

int8 version of NUMERIC?

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

P: n/a
On Sun, Jan 11, 2004 at 21:53:09 +0700,
David Garamond <li***@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).

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

P: n/a
On 12/1/04 3:28 pm, "Bruno Wolff III" <br***@wolff.to> wrote:
On Sun, Jan 11, 2004 at 21:53:09 +0700,
David Garamond <li***@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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #5

P: n/a
Adam Witney wrote:
On 12/1/04 3:28 pm, "Bruno Wolff III" <br***@wolff.to> wrote:
On Sun, Jan 11, 2004 at 21:53:09 +0700,
David Garamond <li***@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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.