Move column data from parent to child table in Postgres

Question

I have a parent and child table, I need to move column information from the parent column to the respective child column.

CREATE TABLE public.parent
(
  id character varying(255) NOT NULL,
  name character varying(255),
  surname character varying(255),
  CONSTRAINT parent_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.parent
  OWNER TO postgres;

CREATE TABLE public.child
(
  id character varying(255) NOT NULL,
  childname character varying(255),
  parentid character varying(255),
  surname character varying(255),
  CONSTRAINT child_pkey PRIMARY KEY (id),
  CONSTRAINT child_parentid_fkey FOREIGN KEY (parentid)
      REFERENCES public.parent (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.child
  OWNER TO postgres;

I have a surname column in parent table, I need to move that data to the respective child.

what I've done so far,

DO $$
DECLARE 
   t_curs cursor for 
      select * from parent;
BEGIN
    FOR t_row in t_curs LOOP
    RAISE NOTICE '%', t_row;
        update child set surname = (select surname from parent where id =  t_row.id) ;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

But with the above example, the last parent surname in the iteration is being updated to all the child tuples.

Is there another approach to do this?

You don't need a loop or PL/pgSQL for this

user330315
– user330315

2020-01-14 15:22:49 +00:00
Commented Jan 14, 2020 at 15:22 — user330315
– user330315, Commented Jan 14, 2020 at 15:22
@Shiva any luck?

Jim Jones
– Jim Jones

2020-01-15 22:47:33 +00:00
Commented Jan 15, 2020 at 22:47 — Jim Jones
– Jim Jones, Commented Jan 15, 2020 at 22:47
Yeah. that works. I have made it complicated unnecessarily.

Shiva kumar
– Shiva kumar

2020-01-20 12:27:51 +00:00
Commented Jan 20, 2020 at 12:27 — Shiva kumar
– Shiva kumar, Commented Jan 20, 2020 at 12:27

Jim Jones · Accepted Answer · 2020-12-15 07:51:49Z

4

Do you really need a PL/pgSQL for this? I believe a simple UPDATE would suffice:

UPDATE public.child
  SET surname = p.surname
FROM public.parent p
WHERE parentid = p.id;

edited Dec 15, 2020 at 7:51

answered Jan 14, 2020 at 15:42

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Move column data from parent to child table in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related