Postgres 16

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 some_table (
  some_int int,
  some_text text
);

CREATE TABLE


insert into some_table(some_int, some_text) values
  (1, '1'), (0, '0'), (null, null);

INSERT 0 3


select
  some_int,
  nullif(some_int, 0),
  some_text,
  nullif(some_text, '0')
from some_table


some_int
nullif
some_text
nullif


1
1
1
1

0
null
0
null

null
null
null
null



SELECT 3


select
  some_int,
  case some_int
    when 0 then null
    else some_int
  end as "case",
  some_text,
  case some_text
    when '0' then null
    else some_text
  end as "case"
from some_table;


some_int
case
some_text
case


1
1
1
1

0
null
0
null

null
null
null
null



SELECT 3

some_int	nullif	some_text	nullif
1	1	1	1
0	null	0	null
null	null	null	null

some_int	case	some_text	case
1	1	1	1
0	null	0	null
null	null	null	null