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 test(a int);
insert into test select 1;

CREATE TABLE

INSERT 0 1

alter table test add column b boolean generated always as (a>=1) stored;

alter table test alter column a type text using a::text;

ALTER TABLE

ERROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "a" is used by generated column "b".

create function test_trigger_function() returns trigger language plpgsql as $$
  declare check boolean;
  begin
  select NEW.a>=1;
  return null;
  end $$;
create trigger test_trigger before insert ON test 
  for each row when (NEW.a>=1) execute function test_trigger_function();

alter table test alter column a type text using a::text;

CREATE FUNCTION

CREATE TRIGGER

ERROR:  cannot alter type of a column used in a trigger definition
DETAIL:  trigger test_trigger on table test depends on column "a"

create view test_view as select a>=1 from test;

alter table test alter column a type text using a::text;

CREATE VIEW

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view test_view depends on column "a"

alter table test add constraint test_constraint check (a>=1);

alter table test alter column a type text using a::text;

ALTER TABLE

ERROR:  operator does not exist: text >= integer
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

create function test_function() returns boolean language sql as $$
select a>=1 from test $$;

alter table test alter column a type text using a::text;

CREATE FUNCTION

ALTER TABLE