Postgres 17

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 test(
   a int generated by default as identity primary key
  ,b boolean,c text,d "char",e "char",f "char");

create table debug_(
   payload jsonb
  ,comment text
  ,is_valid_as_date boolean 
  ,id int generated by default as identity primary key
  ,ts timestamp default clock_timestamp());
select setseed(.46);

create function f_test_trigger()returns trigger as $f$
declare new_jsonb jsonb:=to_jsonb(new);
begin
  insert into debug_ values
   (new_jsonb['c'], 'just one of the columns')
  ,(new_jsonb['x'], 'this column does not exist')
  ,(new_jsonb[chr(97+(random()*25)::int)], '1st random lowercase letter key')
  ,(new_jsonb[chr(97+(random()*25)::int)], '2nd random lowercase letter key');
  update debug_ 
    set is_valid_as_date = pg_input_is_valid( payload#>>'{}' --unquoted text from jsonb scalar
                                             ,'date');
  return new;
end $f$ language plpgsql;

create trigger test_trigger after insert on test
for each row execute function f_test_trigger();

insert into test(b,c,d,e,f)values(true,'2024-10-01','d','e','f');

table debug_;

CREATE TABLE

CREATE TABLE

setseed

SELECT 1

CREATE FUNCTION

CREATE TRIGGER

INSERT 0 1

payload	comment	is_valid_as_date	id	ts
"2024-10-01"	just one of the columns	t	1	2025-01-15 18:26:13.079506
null	this column does not exist	null	2	2025-01-15 18:26:13.079812
null	1st random lowercase letter key	null	3	2025-01-15 18:26:13.079839
"d"	2nd random lowercase letter key	f	4	2025-01-15 18:26:13.079847

SELECT 4