0

I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing this. Child is not referenced to parent table from schema2. Error is Child_test_topic does not exist. Do I need to place the trigger in child schema? And how auto insertion works in this case when new data populated in parent table?

Parent table schema - PUBLIC

CREATE TABLE public.topics_test1
(
  id uuid NOT NULL,
  topic character varying(255) COLLATE pg_catalog."default",
  value character varying(255) COLLATE pg_catalog."default",
  has_synonyms boolean DEFAULT false,
  searchable boolean,
  CONSTRAINT topics_pk11 PRIMARY KEY (id),
  CONSTRAINT topics_pk_test_21 UNIQUE (topic, value)

)
WITH (
  OIDS = FALSE
)
TABLESPACE pg_default;

Child table moved to different schema:

CREATE TABLE child_test_topic (
CONSTRAINT pk_pk11 PRIMARY KEY (id),
CONSTRAINT ck_ck11 CHECK ( topic = 'ingredient' )
) INHERITS (topics_test1);
CREATE INDEX idx_ing ON child_test_topic (topic);

ALTER TABLE child_test_topic SET SCHEMA postgres_partition_tables;

Trigger function and trigger:

CREATE OR REPLACE FUNCTION public.fn_test_schemapropagation() RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.topic = 'ingredient' ) THEN
INSERT INTO child_test_topic VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_topic_trigger1
BEFORE INSERT ON public.topics_test1
FOR EACH ROW EXECUTE PROCEDURE public.fn_test_schemapropagation();

Insert statement:

INSERT INTO public.topics_test1(id, topic, value, has_synonyms, searchable) select * from topics where topic = 'ingredient';
  • If you really need partitioning, the best thing you could do is to upgrade to Postgres 11 to make use of all the new features and way better performance introduced by declarative partitioning – a_horse_with_no_name Aug 19 '19 at 14:51
  • Thank you but i am using cloudSQL in GCP - maximum version they have is 9.6. That's why I am struggling in this. – prabhu Aug 20 '19 at 9:51
  • @a_horse_with_no_name Is there anyother way to achieve this in 9.6? – prabhu Aug 21 '19 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.