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?.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table users(
    userid bigint primary key,
    email text not null,
    created_by text not null default  current_user,
    created_on timestamptz not null default  now(),
    updated_on timestamptz,
    updated_by text
);
create view users_template AS select userid, email from users;
GRANT ALL ON users_template TO public;
CREATE OR REPLACE FUNCTION update_users_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_on = now();
   NEW.updated_by = current_user;
   RETURN NEW;
END;
$$ language 'plpgsql';


CREATE OR REPLACE TRIGGER update_users_column BEFORE UPDATE
ON users FOR EACH ROW EXECUTE PROCEDURE
update_users_column();
insert into  users_template(userid, email) values (1,'hello') returning * 


userid
email


1
hello


table users;


userid
email
created_by
created_on
updated_on
updated_by


1
hello
u_760589783
2022-07-08 17:13:22.737913+01
null
null


update  users set updated_by = 'asjksds' where userid = 1;
update  users set updated_on = '2020-01-01'::timestamptz where userid = 1;
1 rows affected

1 rows affected

table users;


userid
email
created_by
created_on
updated_on
updated_by


1
hello
u_760589783
2022-07-08 17:13:22.737913+01
2022-07-08 17:13:22.739969+01
u_760589783


update users set created_by = 'sjdk ' returning * ;
update users set created_on = '2020-01-01'::timestamptz returning * ;


userid
email
created_by
created_on
updated_on
updated_by


1
hello
sjdk
2022-07-08 17:13:22.737913+01
2022-07-08 17:13:22.742758+01
u_760589783




userid
email
created_by
created_on
updated_on
updated_by


1
hello
sjdk
2020-01-01 00:00:00+00
2022-07-08 17:13:22.742758+01
u_760589783