Postgres 9.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- test1 with 1M rows and 2% NULL
CREATE TABLE test1(num1 numeric, num2 numeric);
INSERT INTO test1
SELECT round((random()* 1000)::numeric, 4)  --  4 fractional digits should be rasonalbe
     , CASE WHEN random() > 0.98 THEN NULL ELSE round((random()* 100)::numeric, 4) END  -- ~ 2 % NULL
FROM generate_series(1, 1000000) g;

-- test2 with 1M rows and 80% NULL
CREATE TABLE test2(num1 numeric, num2 numeric);
INSERT INTO test2
SELECT round((random()* 1000)::numeric, 4)
     , CASE WHEN random() > 0.80 THEN NULL ELSE round((random()* 100)::numeric, 4) END  -- ~ 80 % NULL
FROM generate_series(1, 1000000) g;
1000000 rows affected

1000000 rows affected

-- 4 test candidates
CREATE FUNCTION f_div0_pl_if(val1 numeric, val2 numeric)     
  RETURNS numeric AS
$func$
BEGIN
   IF $2 = 0 THEN
      RETURN NULL;
   ELSE
      RETURN $1 / $2;
   END IF; 
END
$func$  LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION f_div0_pl_nullif(val1 numeric, val2 numeric)     
  RETURNS numeric AS
$func$
BEGIN
   RETURN $1 / NULLIF($2, numeric '0');
END
$func$  LANGUAGE plpgsql IMMUTABLE; 

CREATE FUNCTION f_div0_sql_case(val1 numeric, val2 numeric)     
  RETURNS numeric LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $2 = 0 THEN NULL ELSE $1 / $2 END$$;

CREATE FUNCTION f_div0_sql_nullif(val1 numeric, val2 numeric)     
  RETURNS numeric LANGUAGE sql IMMUTABLE AS
$$SELECT $1 / NULLIF($2, numeric '0')$$;
EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_if(num1, num2)      FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..125630.08 rows=462508 width=64) (actual rows=1000000 loops=1)

Planning time: 1.415 ms

Execution time: 2110.554 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_nullif(num1, num2)  FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..125630.08 rows=462508 width=64) (actual rows=1000000 loops=1)

Planning time: 0.023 ms

Execution time: 1749.985 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_case(num1, num2)   FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..12315.62 rows=462508 width=64) (actual rows=1000000 loops=1)

Planning time: 0.066 ms

Execution time: 639.952 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_nullif(num1, num2) FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..12315.62 rows=462508 width=64) (actual rows=1000000 loops=1)

Planning time: 0.058 ms

Execution time: 573.402 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_if(num1, num2)      FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..121495.36 rows=447286 width=64) (actual rows=1000000 loops=1)

Planning time: 0.066 ms

Execution time: 1969.888 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_nullif(num1, num2)  FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..121495.36 rows=447286 width=64) (actual rows=1000000 loops=1)

Planning time: 0.028 ms

Execution time: 1511.532 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_case(num1, num2)   FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..11910.29 rows=447286 width=64) (actual rows=1000000 loops=1)

Planning time: 0.068 ms

Execution time: 563.925 ms

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_nullif(num1, num2) FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..11910.29 rows=447286 width=64) (actual rows=1000000 loops=1)

Planning time: 0.060 ms

Execution time: 484.588 ms