CODEWITHSUNDEEP
postgresqlsql-insert
miss.traveler
miss.traveler

Reputation: 1

INSERT Values into 2 Tables At Once - postgres

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?

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 1

Related Questions