Question 114230 in PostgreSQL Question posted in
The official documentation can be found here.

Where and how to correctly write a condition in an UPDATE query in postgresql?

There was a moment where I need to calculate the data, but there are empty fields (null) and I wanted him to skip if there is nothing in some fields. Tell me how to prescribe it, otherwise I get an error

ERROR: division by zero
SQL state: 22012

sql query:

UPDATE summary_all_filter_edit SET resul_power = (SELECT round((((all_plats_ok::numeric - all_plats_no::numeric) / all_plats_ok::numeric) * power::numeric), 2) from summary_all_filter_edit su where su.id=summary_all_filter_edit.id);

My table: https://dbfiddle.uk/cXyIdMBr

Tags:

2

Answers


  1. 0

    You may try adding a check in the WHERE clause which prevents an update in the case where the denominator be zero:

    UPDATE summary_all_filter_edit
SET resul_power = (
    SELECT round((((all_plats_ok::numeric - all_plats_no::numeric) /
                    all_plats_ok::numeric) * power::numeric), 2)
FROM summary_all_filter_edit su
WHERE su.id = summary_all_filter_edit.id AND
      all_plats_ok::numeric != 0);
    Login or Signup to reply.
  2. 0

    Whenever we need to use the division operator, I find it helpful to use NULLIF function so that it won’t give me a divison by 0 error.
    In your query, we could add NULLIF to the denominator, so that the result of divison,will be NULL instead of giving you an error

    UPDATE summary_all_filter_edit SET resul_power = (SELECT round((((all_plats_ok::numeric - all_plats_no::numeric) / NULLIF(all_plats_ok::numeric,0)) * power::numeric), 2) from summary_all_filter_edit su where su.id=summary_all_filter_edit.id AND all_plats_ok IS NOT NULL);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search