Inserting Primary ID of one table into another table

Question

I need some help on how to set up a database for user accounts on a mobile device. I have 2 tables: login and settings.

login

id         1
username   test
password   pass

settings

id(same key from login)  1
setting1                 0
setting2                 0
setting3                 0

I have a register page and I want to insert the username, password, and an auto increment primary ID into the login table and I want to insert that same login ID, setting1, setting2, setting3 into the settings table. Can anyone give an example query on how to insert the login ID into both tables at the same time? How do people in the field normally set up user account databases? I will be using web-sql, but you can answer using any sql driver or just plain charts/text.

score 4 · Accepted Answer · answered Aug 20 '12 at 23:51

With PostgreSQL 9.1 or later, you can chain data-modifying statements together in a writeable CTE

WITH x AS (
    INSERT INTO login(id, username, password)
    VALUES (1, 'test', 'pass')
    RETURNING id
    )
INSERT INTO settings(id, setting1, setting2, setting3)
SELECT x.id,0,0,0
FROM   x;

These two statements are executed at the same time - or a close as it gets to be precise.

Also, this construct uses the actual id saved in login, which may be different from what you handed in, if triggers or rules overrule the input. It can also be used for serial primary keys (which is a very common use case) to insert new rows in multiple tables without having to know the new id beforehand. So, no extra round-trip to the server.

If your table login is defined:

CREATE TEMP TABLE login(id serial, username text, password text);

This would work:

WITH x AS (
    INSERT INTO login(username, password)
    VALUES ('test', 'pass')
    RETURNING id
    )
INSERT INTO settings(id, setting1, setting2, setting3)
SELECT x.id,0,0,0
FROM   x;

Just to clarify for readers: As Erwin notes, statements in a wCTE are executed at practically the same time. However, they're still executed serially not in parallel, at least in current Pg versions. More importantly, their execution isn't atomic, they're still subject to many of the same kinds of race conditions and conflicts as separate statements are. wCTEs don't provide a complete answer to a race-free `MERGE` or `UPSERT`, much as you might wish they did, and you still need to think hard about concurrency issues when doing DML via wCTEs. — Craig Ringer, Aug 21 '12 at 00:29

Inserting Primary ID of one table into another table

1 Answers1