CODEWITHSUNDEEP
postgresql
Helloworld12
Helloworld12

Reputation: 15

postreqsql: inserting into 2 tables linked by foreign key so serial id is shared

Hello I'm trying to learn the very basics of Postgresql

How can I insert data (name, lastname) into both tables at the same time so that the serial id connects it(is the same). And then delete one entry in both tables that are connected.

My tables I'm trying to figure that out with:

CREATE TABLE user
(
    "userid" serial NOT NULL,
    name character varying(30),

    PRIMARY KEY ("userid")
);

CREATE TABLE public.passwords
(
    "userid" integer NOT NULL,
    lastname character varying(30),
    CONSTRAINT "user_userid" FOREIGN KEY ("userid")
        REFERENCES public.user ("userid") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
        NOT VALID
);

Upvotes: 1

Views: 48

Answers (1)

GMB
GMB

Reputation: 222602

You can to insert in both tables sequentially with one query, using the serial generated by the first insert in the second insert as follows:

with u as (insert into users (name) values ('foo') returning userid)
insert into passwords (userid, lastname) select userid, 'bar' from u;

Upvotes: 1

Related Questions