0

I am trying to rename parent table in partitions. I created 45 child tables on monthly wise manner. I can alter the name of parent table but I don't know how to alter the 45 child table references.

This is my structure of partition tables. Parent table:

-- Table: public.posts_test

-- DROP TABLE public.posts_test;

CREATE TABLE public.posts_test
(
    id uuid NOT NULL,
    source character varying(255) COLLATE pg_catalog."default" NOT NULL,
    url text COLLATE pg_catalog."default" NOT NULL,
    date_posted timestamp without time zone NOT NULL,
    date_ingested timestamp without time zone NOT NULL,
    social_author_id uuid,
    sentiment integer,
    likes integer,
    reach integer,
    CONSTRAINT posts_pk1 PRIMARY KEY (id),
    CONSTRAINT posts_social_authors_id_fk1 FOREIGN KEY (social_author_id)
        REFERENCES public.social_authors (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.posts_test
    OWNER to postgres;

GRANT ALL ON TABLE public.posts_test TO appengine;

GRANT ALL ON TABLE public.posts_test TO dataflow;

GRANT ALL ON TABLE public.posts_test TO postgres;

GRANT ALL ON TABLE public.posts_test TO test;

-- Trigger: insert_post_date_trigger

-- DROP TRIGGER insert_post_date_trigger ON public.posts_test;

CREATE TRIGGER insert_post_date_trigger
    BEFORE INSERT
    ON public.posts_test
    FOR EACH ROW
    EXECUTE PROCEDURE public.fn_insert_post_date();

Child table :

    CREATE TABLE child_2016_nov (
    CONSTRAINT pk_2016_nov PRIMARY KEY (id),
    CONSTRAINT ck_2016_nov CHECK ( date_posted::date between DATE '2016-11-01' AND  '2016-11-30' )
) INHERITS (posts_test); **---This name need to be changed**
CREATE INDEX idx_2016_nov ON child_2016_nov (date_posted);

I have to change post_test name from all of the places. I can rename parent table and trigger functions but I dont know how to do for all child tables. Please let me know if any methods to achieve without impacting the structure.

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.