Thread: Cast double precision to integer & check for overflow
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. ========================================================================
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> >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)
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
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> > >>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