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.


1 Answer 1


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'
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:

      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 '[email protected]', user_id
FROM ins

Querying the two tables:

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 ║ [email protected] ║       1 ║

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.