Cast double precision to integer & check for overflow

Just cast to integer. Decimal portion will be lost.

Sent from my smartphone

----- Reply message -----
From: "Ian Pilcher" <arequipeno@gmail.com>
To: <pgsql-general@postgresql.org>
Subject: [GENERAL] Cast double precision to integer & check for overflow
Date: Sat, Jan 26, 2013 3:13 pm

I need to cast a double precision into an integer, and I want to check
that the value will actually fit (modulo rounding).

Coming from a C/Java background, this seems like something that should
be utterly trivial. In my searching, however, I can't seem to find any
SQL equivalent of INT_MAX, Integer.MAX_VALUE, etc.

Do I have to hard-code this value?

Thanks!

(And yes, I do feel stupid having to ask this question here.)

--
========================================================================
Ian Pilcher arequipeno@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.
========================================================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/26/2013 05:06 PM, Alexander Gataric wrote:

Just cast to integer. Decimal portion will be lost.

That part I've got. :-)

It's checking that the double precision value will actual fit within the
range of the integer type (-2147483648 to +2147483647). I could
certainly hard-code these values, but I'd like to use a more expressive
syntax, if it is available.

--
========================================================================
Ian Pilcher arequipeno@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.
========================================================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Saturday, January 26, 2013 at 08:13, Ian Pilcher wrote:

I need to cast a double precision into an integer, and I want to check
that the value will actually fit (modulo rounding).

Coming from a C/Java background, this seems like something that should
be utterly trivial. In my searching, however, I can't seem to find any
SQL equivalent of INT_MAX, Integer.MAX_VALUE, etc.

So far I haven't seen such defined constants but am happy to
share the RTFM moment. :-)

I am sure you have already found this:
<http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE&gt;

Do I have to hard-code this value?

Or cast double to both numeric(13000,0) and integer and compare
them. A stored function could encapsulate this along with
raising the exception when required. If design is still fluid
and performance allows the numeric type could do the job without
fear of overflow.

(And yes, I do feel stupid having to ask this question here.)

If in doubt the Novice list is designed for those questions
where feelings of impending stupidity lurk.

Regards
Gavan Schneider (who considers himself a novice)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/26/2013 03:09 PM, Ian Pilcher wrote:

On 01/26/2013 05:06 PM, Alexander Gataric wrote:

Just cast to integer. Decimal portion will be lost.

That part I've got. :-)

It's checking that the double precision value will actual fit within the
range of the integer type (-2147483648 to +2147483647). I could
certainly hard-code these values, but I'd like to use a more expressive
syntax, if it is available.

http://www.postgresql.org/docs/9.2/interactive/catalog-pg-type.html

production=# SELECT typname,typlen from pg_type where typname ='int8';
-[ RECORD 1 ]-
typname | int8
typlen | 8

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2013-01-26, Ian Pilcher <arequipeno@gmail.com> wrote:

I need to cast a double precision into an integer, and I want to check
that the value will actually fit (modulo rounding).

Coming from a C/Java background, this seems like something that should
be utterly trivial. In my searching, however, I can't seem to find any
SQL equivalent of INT_MAX, Integer.MAX_VALUE, etc.

Do I have to hard-code this value?

yes, I think so, the documentation lists the limits here:

http://www.postgresql.org/docs/9.2/interactive/datatype-numeric.html

They are most unlikely to ever change. A new type would probably be added
instead.

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2013-01-26, Gavan Schneider <pg-gts@snkmail.com> wrote:

On Saturday, January 26, 2013 at 08:13, Ian Pilcher wrote:

I need to cast a double precision into an integer, and I want to check
that the value will actually fit (modulo rounding).

Coming from a C/Java background, this seems like something that should
be utterly trivial. In my searching, however, I can't seem to find any
SQL equivalent of INT_MAX, Integer.MAX_VALUE, etc.

So far I haven't seen such defined constants but am happy to
share the RTFM moment. :-)

I am sure you have already found this:
<http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE&gt;

Do I have to hard-code this value?

Or cast double to both numeric(13000,0) and integer and compare
them. A stored function could encapsulate this along with
raising the exception when required. If design is still fluid
and performance allows the numeric type could do the job without
fear of overflow.

there's no need to cast and compare. if the number doesn't fit the cast will
fail with an exception.

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general