Postgres 15

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 tbl (
  name text
, baby_dob1 date
, baby_dob2 date
, baby_dob3 date
, pregnant bool
);

INSERT INTO tbl VALUES
  ('Mary'  , '20-01-2021', '02-03-2022', null        , true)
, ('Esther', '10-01-2020', null        , null        , true)
, ('Helen' , '01-01-2020', '02-02-2021', '09-09-2023', false)
, ('Laura' , null        , null        , null        , true)
, ('Queen' , '25-04-2020', '22-04-2022', '18-07-2022', false)
;

CREATE TABLE



INSERT 0 5


-- with num_nonnulls() and NULLIF()
SELECT num_nonnulls(baby_dob1, baby_dob2, baby_dob3, NULLIF(pregnant, false)) AS "number of pregnancies"
     , count(*) AS "number of women"
FROM   tbl
GROUP  BY 1;


number of pregnancies
number of women


3
3

2
1

1
1



SELECT 3


-- Other variants
SELECT num_nonnulls(baby_dob1, baby_dob2, baby_dob3)
     + pregnant::int AS "number of pregnancies"
     , count(*) AS "number of women"
FROM   tbl
GROUP  BY 1;

SELECT (baby_dob1 IS NOT NULL)::int
     + (baby_dob2 IS NOT NULL)::int
     + (baby_dob3 IS NOT NULL)::int
     + pregnant::int AS "number of pregnancies"
     , count(*) AS "number of women"
FROM   tbl
GROUP  BY 1;


number of pregnancies
number of women


3
3

2
1

1
1



SELECT 3




number of pregnancies
number of women


3
3

2
1

1
1



SELECT 3