Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE player (firstname varchar, lastname varchar, h_feet INT, h_inches INT);
INSERT INTO player VALUES ('Jimmy', 'Howard', 6, 2);

-- SQL function with aggregate function and no GROUP BY always returns exactly 1 row
CREATE OR REPLACE FUNCTION get_height_sql(firstn varchar, lastn varchar)
  RETURNS float
  LANGUAGE sql STABLE AS
$func$
SELECT COALESCE(avg(((p.h_feet * 12) + p.h_inches) * 2.54)::float, 0)
FROM   player p
WHERE  p.firstname = firstn
AND    p.lastname = lastn
$func$;
1 rows affected

SELECT get_height_sql('Jimmy', 'Howard') AS a
     , get_height_sql('non', 'existent') AS x;


a
x


187.96
0


-- no row
SELECT *
FROM   player
WHERE  firstname = 'non'
AND    lastname = 'existent';
-- 1 row with a NULL value
SELECT AVG(((h_feet * 12) + h_inches) * 2.54)::float
FROM   player
WHERE  firstname = 'non'
AND    lastname = 'existent';


avg


null


-- 1 row, but never with NULL:
SELECT COALESCE(AVG(((h_feet * 12) + h_inches) * 2.54)::float, 0)
FROM   player
WHERE  firstname = 'non'
AND    lastname = 'existent';


coalesce


0