Ошибка деление на ноль postgresql

I am trying to run the following query which results me postgres error: division by zero

select 
    request_count,
    response_count, 
    (response_count*100) / (request_count+response_count) AS proportion 
from total_dummy_table; 

How can I avoid and resolve the division by zero error?

Tried to fix using the below query but getting the same result as above

SELECT 
    request_count,
    response_count,
    (response_count*100) / (request_count) AS proportion 
FROM 
    total_dummy_table
ORDER BY 
    (response_count*100) /(CASE request_count WHEN 0 Then NULL ELSE request_count END) DESC NULLS FIRST

Please let me know where am I doing wrong, or how can I fix this.
Thanks!

Result expectation:

The query should return me something like below:

Request_count | Response_count | Proportion

1603423       |  585706        | 36.52

Quick note: Table total_dummy_table does not have column name proportion that is the addition to the result which have calculated proportion in it.

I am trying to run the following query which results me postgres error: division by zero

select 
    request_count,
    response_count, 
    (response_count*100) / (request_count+response_count) AS proportion 
from total_dummy_table; 

How can I avoid and resolve the division by zero error?

Tried to fix using the below query but getting the same result as above

SELECT 
    request_count,
    response_count,
    (response_count*100) / (request_count) AS proportion 
FROM 
    total_dummy_table
ORDER BY 
    (response_count*100) /(CASE request_count WHEN 0 Then NULL ELSE request_count END) DESC NULLS FIRST

Please let me know where am I doing wrong, or how can I fix this.
Thanks!

Result expectation:

The query should return me something like below:

Request_count | Response_count | Proportion

1603423       |  585706        | 36.52

Quick note: Table total_dummy_table does not have column name proportion that is the addition to the result which have calculated proportion in it.

Будьте внимательны во время деления целых чисел

В PostgreSQL деление целого числа на целое число в результате дает целое число. Не делайте так:

db=# (
  SELECT tax / price AS tax_ratio
  FROM sale
);
 tax_ratio
----------
    0

Чтобы получить ожидаемый результат деления, вам нужно привести одно из значений к плавающему типу:

 db=# (
  SELECT tax / price::float AS tax_ratio
  FROM sale
);
 tax_ratio
----------
 0.17

Неспособность распознать эту ошибку может привести к крайне неточным результатам.

Защита от ошибок деления на ноль

Деление на ноль — известная ошибка:

db=# SELECT 1 / 0
ERROR: division by zero

Деление на ноль является логической ошибкой и ее нужно не просто «обойти», а исправить так, чтобы на первом месте у вас не было делителя, равного нулю. Однако бывают ситуации, когда возможен нулевой делитель. Один из простых способов защиты от ошибок деления на ноль — это присвоить всему выражению неопределенное значение, установив неопределенное значение делителю, если он равен нулю:

db=# SELECT 1 / NULLIF(0, 0);
 ?column?
----------
   -

Функция NULLIF возвращает неопределенное значение null, если первый аргумент равен второму. В этом случае, если знаменатель равен нулю.

При делении любого числа на NULL результатом будет NULL. Чтобы получить некоторое значение, вы можете свернуть все выражение с COALESCE и предоставить дефолтное значение:

db=# SELECT COALESCE(1 / NULLIF(0, 0), 1);
 ?column?
----------
    1

Функция COALESCE очень полезна. Она допускает любое количество аргументов и возвращает первое значение, которое не является неопределенным.

Знайте разницу между UNION и UNION ALL

Классический вопрос на собеседовании начального уровня для разработчиков и администраторов баз данных: «В чем разница между функциями UNION и UNION ALL?».

UNION ALL объединяет результаты одного или нескольких запросов. UNION делает то же самое, и к тому же исключает повторяющиеся строки. Не делайте так:

 SELECT created_by_id FROM sale
  UNION
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
Unique  (cost=2654611.00..2723233.86 rows=13724572 width=4)
  ->  Sort  (cost=2654611.00..2688922.43 rows=13724572 width=4)
        Sort Key: sale.created_by_id
        ->  Append  (cost=0.00..652261.30 rows=13724572 width=4)
              ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
              ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

В плане выполнения вы можете видеть, что после добавления двух запросов база данных отсортировала результаты и исключила повторяющиеся строки.

Если вам не нужно удалять повторяющиеся строки, лучше использовать функцию UNION ALL:

 db=# (
  SELECT created_by_id FROM sale
  UNION ALL
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
 Append  (cost=0.00..515015.58 rows=13724572 width=4)
   ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
   ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

Выполняется намного проще. Результаты получены, сортировка не требуется.

Будьте внимательны при подсчете столбцов, допускающих неопределенное значение

При использовании агрегатных функций, таких как COUNT, важно понимать, как они обрабатывают неопределенные значения.
Например, возьмите следующую таблицу:

db=# pset null NULL
Null display is "NULL".
db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT *
FROM tb;
  id
------
    1
 NULL

Столбец id содержит значение null. Посчитаем столбец id:

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(id)
FROM tb;
 count
-------
     1

В таблице две строки, но функция COUNT возвращает 1. Это произошло, потому что функция COUNT игнорирует неопределенные значения.

Чтобы посчитать строки, используйте функцию COUNT(*):

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(*)
FROM tb;
 count
-------
  2

Эта особенность также может быть полезна. Например, если поле с именем modified содержит неопределенное значение в том случае, если строка не была изменена, вы можете рассчитать процент измененных строк следующим образом:

db=# (
  SELECT COUNT(modified) / COUNT(*)::float AS modified_pct
  FROM sale
);
 modified_pct
---------------
  0.98

Другие агрегатные функции, такие как SUM, будут игнорировать неопределенные значения. Для демонстрации применим функцию SUM к полю, содержащему только неопределенные значения:

db=# WITH tb AS (
  SELECT null AS id
  UNION ALL
  SELECT null AS id
)
SELECT SUM(id::int)
FROM tb;
 sum
-------
 NULL

Это все документированные операции, так что будьте внимательны!

Обратите внимание на часовые пояса

Часовые пояса всегда являются источником путаницы и ошибок. PostgreSQL отлично справляется с часовыми поясами, но вам все равно придется обратить внимание на некоторые вещи.

Распространенной ошибкой, которую я вижу бесчисленное количество раз, является сокращение временных меток без уточнения часового пояса. Скажем, мы хотим узнать, сколько продаж было сделано каждый день:

SELECT created_at::date, COUNT(*)
FROM sale
GROUP BY 1

Без точной настройки часового пояса вы можете получить разные результаты в зависимости от часового пояса, установленного приложением:

 now
------------
 2019-11-08
db=# SET TIME ZONE 'australia/perth';
SET
db=# SELECT now()::date;
now
------------
2019-11-09

Если вы не уверены, с каким часовым поясом работаете, вы можете делать это неправильно.

При обращении к временной метке сначала приведите ее к нужному часовому поясу:

SELECT (timestamp at time zone 'asia/tel_aviv')::date, COUNT(*)
FROM sale
GROUP BY 1;

За установку часового пояса обычно отвечает приложение. Например, чтобы получить часовой пояс, используемый psql:

db=# SHOW timezone;
TimeZone
----------
Israel
db=# SELECT now();
now
-------------------------------
2019-11-09 11:41:45.233529+02

А чтобы установить часовой пояс в PSQL:

 db=# SET timezone TO 'UTC';
SET
db=# SELECT now();
now
-------------------------------
2019-11-09 09:41:55.904474+00

Также важно помнить, что часовой пояс вашего сервера может отличаться от часового пояса вашего локального компьютера. Поэтому если вы выполняете запросы на локальном компьютере, они могут привести к разным результатам в готовом продукте. Чтобы избежать ошибок, всегда точно устанавливайте часовой пояс.

Избегайте преобразований в индексированных полях

Использование функций в индексированном поле может помешать базе данных использовать индекс в этом поле:

SELECT * FROM sale
  WHERE created at time ZONE 'asia/tel_aviv' > '2019-10-01'
);
QUERY PLAN
----------
Seq Scan on sale (cost=0.00..510225.35 rows=4523386 width=276)
Filter: timezone('asia/tel_aviv', created) > '2019-10-01 00:00:00'::timestamp without time zone

Поле created индексируется, но поскольку мы преобразовали его с помощью часового пояса, индекс не использовался.

Один из способов использования индекса в этом случае — применить преобразование в правой части:

SELECT * FROM sale WHERE created > '2019-10-01' AT TIME ZONE 'asia/tel_aviv' );
QUERY PLAN
----------
Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
Index Cond: (created > '2019-10-01 00:00:00'::timestamp with time zone)

Другим примером использования дат является фильтрация определенного периода:

 db=# (
 SELECT * FROM sale WHERE created + INTERVAL '1 day' > '2019-10-01'
);
QUERY PLAN
----------
 Seq Scan on sale  (cost=0.00..510225.35 rows=4523386 width=276)
   Filter: ((created + '1 day'::interval) > '2019-10-01 00:00:00+03'::timestamp with time zone)

Как и до этого, интервальная функция в поле created препятствовала базе данных использовать индекс. Чтобы база данных использовала индекс, примените преобразование в правой части выражения вместо всего поля:

 SELECT *
  FROM sale
  WHERE created > '2019-10-01'::date - INTERVAL '1 day'
);
QUERY PLAN
----------
 Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
   Index Cond: (created > '2019-10-01 00:00:00'::timestamp without time zone)

Заключение

Применение приведенных выше советов в повседневной жизни помогает нам поддерживать работоспособную базу данных с минимальными потерями. Мы обнаружили, что обучение разработчиков и не разработчиков тому, как лучше писать на SQL, может иметь большое значение. Если у вас есть какие-то советы по SQL, которые мы могли пропустить, дайте нам знать, и мы добавим их сюда!

Во встроенном процедурном языке PL/pgSQL для СУБД PostgreSQL отсутствуют привычные операторы TRY / CATCH для для перехвата исключений возникающих в коде во время выполнения. Аналогом является оператор EXCEPTION, который используется в конструкции:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS -- аналог catch  
THEN
    -- код, обрабатывающий исключение
END

Если необходимо обработать только конкретную ошибку, то в условии WHEN нужно указать идентификатор или код конкретной ошибки:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN '<идентификатор_или_код_ошибки>'
THEN
    -- код, обрабатывающий исключение
END

Внутри секции EXCEPTION код ошибки можно получить из переменной SQLSTATE, а текст ошибки из переменной SQLERRM:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS  
THEN
    RAISE NOTICE 'ERROR CODE: %. MESSAGE TEXT: %', SQLSTATE, SQLERRM;
END

Более подробную информацию по исключению можно получить командой GET STACKED DIAGNOSTICS:

BEGIN
  -- код, в котором может возникнуть исключение
EXCEPTION WHEN OTHERS  
THEN
		GET STACKED DIAGNOSTICS
    	err_code = RETURNED_SQLSTATE, -- код ошибки
		msg_text = MESSAGE_TEXT, -- текст ошибки
    	exc_context = PG_CONTEXT, -- контекст исключения
 		msg_detail = PG_EXCEPTION_DETAIL, -- подробный текст ошибки
 		exc_hint = PG_EXCEPTION_HINT; -- текст подсказки к исключению

    RAISE NOTICE 'ERROR CODE: % MESSAGE TEXT: % CONTEXT: % DETAIL: % HINT: %', 
   	err_code, msg_text, exc_context, msg_detail, exc_hint;
END

Полный список переменных, которые можно получить командой GET STACKED DIAGNOSTICS:

Пример обработки исключения

В качестве примера будет рассмотрена обработка ошибки деления на ноль в функции catch_exception:

CREATE OR REPLACE FUNCTION catch_exception
(
	arg_1 int,
	arg_2 int,
	OUT res int
)
LANGUAGE plpgsql
AS $$
DECLARE 
	err_code text;
	msg_text text;
	exc_context text;
BEGIN
	BEGIN
		res := arg_1 / arg_2;
	EXCEPTION 
	WHEN OTHERS 
  THEN
		res := 0;
    
		GET STACKED DIAGNOSTICS
    	err_code = RETURNED_SQLSTATE,
		msg_text = MESSAGE_TEXT,
    	exc_context = PG_CONTEXT;

   		RAISE NOTICE 'ERROR CODE: % MESSAGE TEXT: % CONTEXT: %', 
   		err_code, msg_text, exc_context;
  END;
END;
$$;

Вызов функции catch_exception со значением 0 в качестве второго параметра вызовет ошибку деления на ноль:

DO $$
DECLARE 
	res int;
BEGIN
	SELECT e.res INTO res
	FROM catch_exception(4, 0) AS e;
	
	RAISE NOTICE 'Result: %', res;
END;
$$;

Результаты обработки ошибки будут выведены на консоль:

ERROR CODE: 22012 
MESSAGE TEXT: деление на ноль 
CONTEXT: функция PL/pgSQL catch_exception(integer,integer), строка 14, оператор 
GET STACKED DIAGNOSTICS
SQL-оператор: "SELECT e.res FROM catch_exception(4, 0) AS e"
функция PL/pgSQL inline_code_block, строка 5, оператор SQL-оператор
Result: 0