pgsql integer out of range for negative values

Question

It might sound an easy question to most of you. However, I'm unable to avoid this error. I have a table with id(primary_key) with bigint datatype and we started feeding records into that from min -negative value (-9223372036854775808).

Now, I need to do some operation on that table based on each record data. So, I need to iterate through all records. This is the sample loop, I'm using and I'm getting below error with that:

    psql:update-migration2.sql:39: ERROR:  integer out of range
 CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR with integer loop variable

Below , is the sample loop where I'm getting this error:

 do $$
declare
i bigint;
begin
for i in -9223372036854775808 ..  -9223372036852105062 loop
-- my logic
i := i + 1;
end loop;
end;$$

Any immediate help/solution will be really appreciated.

pifor · Accepted Answer · 2020-05-18 15:58:05Z

1

FOR ... LOOP only works on integer (-2147483648 to +2147483647)

This is OK:

do $$
declare
i bigint;
begin
for i in -2147483648 .. -2147483647  loop
-- my logic
end loop;
end;$$
DO

This fails:

do $$
declare
i bigint;
begin
for i in -2147483649 .. -2147483647  loop
-- my logic
end loop;
end;$$
ERROR:  integer out of range
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR with integer loop variable

answered May 18, 2020 at 15:58

pifor

7,9722 gold badges12 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

pgsql integer out of range for negative values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related