integer out of range error when merging two tables

[ivansz]

I have a Power BI report getting data from multiple tables in PostgreSQL database. The model has been working well for a while, but since today I get an error when I want to refresh the data: "OLE DB or ODBC error: [DataSource.Error] PostgreSQL: 22003: integer out of range." The strange thing is that I get this error at the step when I merge two tables. Reading data from table1 works fine. Readind data from table2 works fine too. But at the step where I merge queries using inner join. I get the error. If I remove the merge the data refresh works fine. The column to do the mege is an integer (int8 in PosgreSQL) and it is the Primary key-Foreign Key of the two tables in the database. Any idea what can be the problem? Or how can I determine which value casues the issue. (Max value in the column is 216380.)

[Alex87 · Accepted solution]

The error you're encountering, "OLE DB or ODBC error: [DataSource.Error] PostgreSQL: 22003: integer out of range," suggests that during the data processing within Power BI (especially during the merging step), an integer value is being processed that exceeds the allowable range for the data type expected by the PostgreSQL database. In PostgreSQL, an int8 is an 8-byte integer, which should be more than sufficient to handle any integer values.

Check your data types in Power Bi, try to examine the merge process and simplify it to debug, validate the data integrity by looking for extreme values that could generate the error.

[ivansz · Accepted solution]

Finally, I found the root cause of the problem: One of the queries reads data from a view in the database and in that view one of the calculated columns got this "out of range" error. What is still a mystery that when I removed the merging in PowerBI all table refreshed without a problem (seemingly).

[Alex87 · Accepted solution]

The error you're encountering, "OLE DB or ODBC error: [DataSource.Error] PostgreSQL: 22003: integer out of range," suggests that during the data processing within Power BI (especially during the merging step), an integer value is being processed that exceeds the allowable range for the data type expected by the PostgreSQL database. In PostgreSQL, an int8 is an 8-byte integer, which should be more than sufficient to handle any integer values.

Check your data types in Power Bi, try to examine the merge process and simplify it to debug, validate the data integrity by looking for extreme values that could generate the error.