0

Given a users and a roles table, I want to write an SQL statement, which inserts a new row into the users table and associates a row of the roles table with it.

This is what I have tried:

INSERT INTO users(firstname, lastname) VALUES ('John', 'Smith') RETURNING id;

INSERT INTO roles(role, user_id)
VALUES ('ROLE_USER', id);

The id used in the last line is not yet associated with the return value of the first line. Can someone explain to me how I could store the return type of the first line inside a variable and then use it in my last line?

I have come across the DECLARE keyword, but I am not sure this works with every kind of database. I am using a postgres DB. When using DECLARE @VariableName, the @ gets marked as wrong syntax in my db-migrate script.

Luk
  • 1,009
  • 2
  • 15
  • 33

1 Answers1

1

You can use a data modifying CTE:

with new_user as (
  INSERT INTO users(firstname, lastname) 
  VALUES ('John', 'Smith') 
  RETURNING id
)
INSERT INTO roles(role, user_id)
SELECT 'ROLE_USER', id
FROM new_user;
  • this works beautifully, thx so much! ... I am not sure I understand it completely, though: What does the WITH statement return? I believe new_user should be an id, but this doesn't seem to be correct as the following SQL fails: `with new_user_id as ( INSERT INTO users(firstname, lastname) VALUES ('John', 'Smith') RETURNING id ) INSERT INTO roles(role, new_user_id); ` – Luk Dec 05 '22 at 11:25
  • The CTE returns the generated ID. But you can't access it "out of thin air". You have to use a SELECT to access the column (and row) from the CTE. –  Dec 05 '22 at 11:35