PLPGSQL function returns ERROR: integer out of range even if value is correct

Question

I'm experiencing strange behaviour on two Postgres functions returning a single bigint value. The first one returns the correct value while the second one throws an error:

ERROR: integer out of range

The only difference is that the first one returns a constant time value in ns, while the second one calculates it based on constant values, for convenience, given in s:

This one works fine:

CREATE OR REPLACE FUNCTION c_getTime1()
RETURNS bigint AS $$
BEGIN
    RETURN 12000000000; --12s in ns
END; $$
LANGUAGE plpgsql IMMUTABLE parallel safe;

This one throws an error.

CREATE OR REPLACE FUNCTION c_getTime2()
RETURNS bigint AS $$
BEGIN
    RETURN 12*1000*1000*1000; --12s in ns
END; $$
LANGUAGE plpgsql IMMUTABLE parallel safe;

Just wondering, is there any big difference? Should I somehow type-cast the second function's return? Both functions are called using direct way:

select c_getTime1();
select c_getTime2();

The Postgresql database version is 13.3.

Thanks in advance for any suggestions.

jian · Accepted Answer · 2022-05-07 08:06:47Z

1

select pg_typeof(100); return integer.
RETURN 12*1000*1000*1000; will first compute as integer then cast to bigint. https://www.postgresql.org/docs/current/datatype-numeric.html first compute then found out ERROR: 22003: integer out of range so you should change to

RETURN 12 ::bigint * 1000 ::bigint * 1000::bigint * 1000::bigint;

answered May 7, 2022 at 8:06

jian

5,0231 gold badge29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Klaver Over a year ago

All you need to do is: select 12*1000*1000*1000::bigint; 12000000000

Collectives™ on Stack Overflow

PLPGSQL function returns ERROR: integer out of range even if value is correct

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related