INSERT Values into 2 Tables At Once - postgres

Question

I have two tables and I'm trying to add data into both at the same time, but I'm having trouble getting my insert to work.

The tables are:

CREATE TABLE "user" (
    "id" SERIAL PRIMARY KEY,
    "username" VARCHAR (80) UNIQUE NOT NULL,
    "password" VARCHAR (1000) NOT NULL,
    "admin" boolean DEFAULT FALSE
);

CREATE TABLE "user_info" (
"id" SERIAL PRIMARY KEY,
"first_name" varchar(60) NOT NULL,
"last_name" varchar(60) NOT NULL,
"email" varchar(120),
"street" varchar(200),
"city" varchar(200),
"state" varchar(2),
"zipcode" varchar(20),
"phone" INT,
"user_login_id" INT REFERENCES "user"
);

The code I tried was:

WITH "user_insert" as (
INSERT INTO "user" ("username", "password") 
VALUES ('larry', 'root')
RETURNING "id" as "user_login_id"
),
"second_insert" as (
INSERT INTO "user_info"("first_name", "last_name", "email", "street", "city", "state", "zipcode", "phone", "user_login_id")
VALUES ('larry', 'marry', '[email protected]', '222 adam street', 'saint paul', 'MN', '55101', '5551113333', 
(select id from "user")
);

What I expected was to add those values into their respective tables. Can someone help please?

Use stored function.

Abelisto
– Abelisto

2019-10-19 05:37:24 +00:00
Commented Oct 19, 2019 at 5:37 — Abelisto
– Abelisto, Commented Oct 19, 2019 at 5:37

Tometzky · Accepted Answer · 2019-10-20 09:24:11Z

1

First, you need a SQL statement to follow the CTEs.

Also, I doubt you need all the double quotes. It is really better to define tables and columns without them.

I think the following should work:

WITH u as (
     INSERT INTO user (username, password)
         VALUES ('larry', 'root')
         RETURNING id
     )
INSERT INTO user_info (first_name, last_name, email, street, city, state, zipcode, phone, user_login_id) 
    SELECT 'larry', 'marry', '[email protected]', '222 adam street', 'saint paul', 'MN', '55101', '5551113333', u.id
    FROM u;

edited Oct 20, 2019 at 9:24

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

answered Oct 19, 2019 at 0:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

INSERT Values into 2 Tables At Once - 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