PostgreSQL: INSERTING a record into a child table when the parent table already exists

1.1k Views Asked by At

I have two tables where there is a record on the parent table where I want to create a child record that builds on the record. The detail of what I did is below:

Having two tables created with a serial PK.

CREATE TABLE IF NOT EXISTS public.parent
(
    parent_id smallserial NOT NULL,
    parent_field character varying(64),
    PRIMARY KEY (parent_id)
)


CREATE TABLE public.child
(
    child_id smallserial,
    child_field character varying(64),
    PRIMARY KEY (child_id)
)
    INHERITS (public.parent);

And then carrying out a:

INSERT INTO parent (parent_field) VALUES ('Hello World');

The ID the sequence gave was 4. I then tried running this query to build on that field on the child table:

INSERT INTO child (parent_id, parent_field, child_field)
VALUES (4, 'Hello World', 'The child');

But I ended up with two records on the parent table: enter image description here

If I only want to have one parent record with that PK, what is the proper way to do this?

0

There are 0 best solutions below