Check out our free eBook: "Best Practices for Optimizing Postgres Query Performance"Download Now

U128: Division by zero

Category: Application / User Errors
SQLSTATE: 22012 (Class 22 — Data Exception: division_by_zero)
Urgency: low

Example Postgres Log Output:

ERROR: division by zero
STATEMENT: SELECT 1/0

Explanation:

Whilst executing the statement, Postgres had to perform a division by zero, which is not allowed.

Note that this applies to both integer divisions by zero (1 / 0) as well as floating point divisions by zero (1 / 0.0).

Often this occurs due to a value thats returned from a table, so it may be unclear at first where the problematic zero is coming from.

Recommended Action:

In simple cases, the problematic expression can simply be removed. However that may often prove difficult, especially when the source data is user controlled.

One way to resolve this issue on user generated data, is to utilize NULLIF like this:

SELECT 1.0 / NULLIF(column_that_may_be_zero, 0)

This will return the result of the division in cases where the column is not zero, and return NULL in the cases where it is zero, instead of erroring out.

Learn More:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →