PostgreSQL – Insert Data and Use Primary Key in Another Table

postgresql

I have a quick question. I have a table users

 -----------------------------
| ID | first_name | last_name |
 -----------------------------

and another table like users.login

 ------------------
| users_ID | EMAIL |
 ------------------

My id's in the first table are auto incrementing from a certain point, say 160,000. How do I take the id from the first table, save it to a value, and then put that value into the id of the 2nd table? The id in users.login is foreign key to users.id.

So far i have

INSERT INTO public.users (first_name, last_name)
                VALUEs ($1,$2) RETURNING ID;
INSERT INTO public.users_login(email, ID)
                Values($1, id)

does this work? I'm new to postgres.

Best Answer

Consider using the RETURNING clause on an INSERT or UPDATE statement to insert the foreign key. From the PostgreSQL docs:

WITH upd AS (
  UPDATE employees 
  SET sales_count = sales_count + 1 
  WHERE id =
    (
       SELECT sales_person 
       FROM accounts 
       WHERE name = 'Acme Corporation'
    )
    RETURNING *
)
INSERT INTO employees_log 
SELECT *, current_timestamp FROM upd;

So, your statement would need to do the initial insert in common-table-expression, which returns the id to allow it to be inserted into the users_login table:

CREATE TABLE users (
      user_id serial
    , first_name varchar(30)
    , last_name varchar(30)
);

CREATE TABLE users_login (
      user_login_id serial
    , email varchar(30)
    , user_id int
);

WITH ins AS (
    INSERT INTO users (first_name, last_name)
                VALUES ('hannah', 'vernon') 
    
    RETURNING user_id
)
INSERT INTO users_login(email, user_id)
SELECT 'us@them.com', user_id
FROM ins

Querying the two tables:

SELECT *
FROM users u
    INNER JOIN users_login ul ON u.user_id = ul.user_id;

╔═════════╦════════════╦═══════════╦═══════════════╦═════════════╦═════════╗
║ user_id ║ first_name ║ last_name ║ user_login_id ║    email    ║ user_id ║
╠═════════╬════════════╬═══════════╬═══════════════╬═════════════╬═════════╣
║       1 ║ hannah     ║ vernon    ║             1 ║ us@them.com ║       1 ║
╚═════════╩════════════╩═══════════╩═══════════════╩═════════════╩═════════╝
Related Question