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

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

CodePudding user response：

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);

CodePudding user response：

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);