ERROR: integer out of range In postgresql

Question

Below is my table schema (I am using PostgreSQL 9.6.1)

  my_table
  ------------
  table_id SERIAL PRIMRY KEY,
  A1 INTEGER,
  A2 INTEGER,
  A3 INTEGER,
  A4 INTEGER,
  A5 INTEGER,
  A6 INTEGER,
  A7 INTEGER,
  A8 INTEGER,
  A9 INTEGER,
  A10 INTEGER,
  A11 INTEGER,
  A12 INTEGER,
  A13 INTEGER,
  A14 INTEGER,
  A15 INTEGER,
  A16 DOUBLE PRECISION,
  A17 DOUBLE PRECISION

and i am trying to run below query in order to create a new table.

CREATE TABLE my_table_2 AS
  SELECT
    B1                                              AS C1                           
    B3                                              AS C2,
    B5                                              AS C3,
    B7                                              AS C4 ,
    B9                                              AS C5,
    B11                                             AS C6 ,
    B1 / CAST( NULLIF(B3, 0) AS FLOAT)              AS C7,
    B1 / CAST( NULLIF(B5, 0) AS FLOAT)         AS C8,
    B1 / CAST( NULLIF(B7, 0) AS FLOAT)        AS C9,
    B1 / CAST( NULLIF(B9, 0) AS FLOAT)     AS C10,
    B1 / CAST( NULLIF(B11, 0) AS FLOAT)     AS C11,
    B3 / CAST( NULLIF(B5, 0) AS FLOAT)      AS C12,
    B5 / CAST( NULLIF(B7, 0 )* NULLIF(B9,0) AS FLOAT)  AS C13,

    B2                                    AS C14 ,
    B4                                  AS C15,
    B6                                    AS C16,
    B8                                  AS C17,
    B10                                AS C18,
    B12                                AS C19,
    B2 / CAST(NULLIF(B4, 0) AS FLOAT)  AS C20,
    B2 / CAST(NULLIF(B6, 0) AS FLOAT)  AS C21,
    B2 / CAST(NULLIF(B8, 0) AS FLOAT)  AS C22,
    B2 / CAST(NULLIF(B10, 0) AS FLOAT) AS C23,
    B2 / CAST(NULLIF(B12 , 0) AS FLOAT) AS C24
    B4 / CAST(NULLIF(B6 , 0) AS FLOAT)  AS C25
    B6 / CAST(NULLIF(B8,0) * NULLIF( B10,0) AS FLOAT) AS C26

    0.0 AS C27, 
    0.0 AS C28,
    0.0 AS C29,
    0.0 AS C30,
    0.0 AS C31,
    0.0 AS C32,
    0.0 AS C33,
    0.0 AS C34,
    0.0 AS C35,
    0.0 AS C36,
    0.0 AS C37,
    0.0 AS C38,
    0.0 AS C39,

    B13 AS C40
    B14 AS C41,
    B15 AS C42

  FROM (
         SELECT
           table_id,
           A1 - A2 + A3  AS B1,
           A1            AS B2,
           A4 - A5       AS B3,
           A4            AS B4,
           A6 - A7       AS B5,
           A6            AS B6,
           A8 - A9       AS B7,
           A8            AS B8,
           A10 - A11     AS B9,
           A10           AS B10,
           A12 - A13     AS B11,
           A12           AS B12,
           A14           AS B13,
           A15           AS B14,
           coalesce(coalesce(A16 * 100, 0) / NULLIF(A17, 0), 0) AS B15
         FROM my_table    
       ) v1;

I am running the above query as a background process using psql command using below command

sudo -u postgres psql My_DB -X -a -f /tmp/test.sql > result.out 2>result.err &

Since my_table is a very huge table. But after sometime when I check result.err file. It has only one line saying

ERROR: integer out of range

For some sample ids in the WHere clause table gets generated without any issues but when i run it for entire table above error comes.

Can anyone explain why is this happening and how I can fix this?

cast every column where you have multiply ordivide opearation to bigint, eg (A6 - A7)::bigint as B5 — Vao Tsun
– Vao Tsun, Commented Feb 5, 2018 at 6:53

Laurenz Albe · Accepted Answer · 2018-02-05 07:26:14Z

1

Any of the + or - operations could cause this error, but the most likely cause is A16 * 100.

You can fix the problem by either avoiding operations that cause the overflow, or you could choose a data type like numeric or bigint that has a wider range of values.

answered Feb 5, 2018 at 7:26

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Sergey Gershkovich · Accepted Answer · 2018-02-05 09:05:32Z

1

check the multiplications

NULLIF(B7, 0 )* NULLIF(B9,0)

and

NULLIF(B8,0) * NULLIF( B10,0)

try to replace the order of calculation, the first division, then multiplication

( B5 / NULLIF(B7, 0) ) * NULLIF(B9,0) AS B15
( B6 / NULLIF(B8, 0) ) * NULLIF(B10,0) AS C26

answered Feb 5, 2018 at 9:05

Sergey Gershkovich

4223 silver badges6 bronze badges

Collectives™ on Stack Overflow

ERROR: integer out of range In postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related