I am trying to insert in child table ONLY the same record that is already exsisting in the parent table.
Because ONLY is not aplicable i've tried to create before insert trigger on parent table that checks for existing id(primary key) but it seems it doesn`t work -> the data are still duplicated:
parent table:
CREATE TABLE public.store(
id serial PRIMARY KEY,
name text);
child table :
CREATE TABLE public.db_store(
) INHERITS(store);
alter table public.db_store
add constraint db_store_pkey_id primary key (id);
function trigger:
create or replace function store_before_insert()
returns trigger language plpgsql as $$
declare old_s_id integer;
if (new.id is not null ) then
select s.id into old_s_id from store s where s.id=new.id;
if (old_s_id is not null) then
return null;
end if;
end if;
return new;
end $$;
trigger itself:
create trigger insert_storehouse_trg
before insert on storehouse
for each row
execute procedure storehouse_before_insert();
if execute:
insert into store(name) ('test');
insert into db_store(id,name) (1,'test');
(1, 'test') appers two time in store.
Is there are way to achive it without delete record from parent table?

Check Solution