Add serial value across multiple tables

Question

I have the following two tables in my Postgres database:

CREATE TABLE User (
    Id       serial UNIQUE NOT NULL, 
    Login    varchar(80) UNIQUE NOT NULL,
PRIMARY KEY (Id,Login)
);

CREATE TABLE UserData (
    Id       serial PRIMARY KEY REFERENCES Users (Id),
    Password varchar(255) NOT NULL
);

Say, I add a new user with INSERT INTO Users(Id, Login) VALUES(DEFAULT, 'John') and also want to add VALUES(id, 'john1980') in UserData where id is John's new id.

How do I get that id? Running a query for something just freshly created seems superfluous. I have multiple such situations across the database. Maybe my design is flawed in general?

(I'm obviously not storing passwords like that.)

Answer 1

1) Fix your design

CREATE TABLE usr (
    usr_id serial PRIMARY KEY,
   ,login  text UNIQUE NOT NULL
);

CREATE TABLE userdata (
    usr_id   int PRIMARY KEY REFERENCES usr
   ,password text NOT NULL
);

• Start by reading the manual about identifiers and key words.

  • user is a reserved word. Never use it as identifier.
  • Use descriptive identifiers. id is useless.
  • Avoid mixed case identifiers.

• serial is meant for a unique column that can be pk on its own. No need for a multicolumn pk.

• The referencing column userdata.usr_id cannot be a serial, too. Use a plain integer.

• I am just using text instead of varchar(n), that's optional. More here.

• You might consider to merge the two tables into one ...

2) Query to INSERT in both

Key is the RETURNING clause available for INSERT, UPDATE, DELETE, to return values from the current row immediately.
Best use in a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO usr(login)
   VALUES ('John')             -- just omit default columns
   RETURNING usr_id            -- return automatically generated usr_id
   )
INSERT INTO userdata (usr_id, password )
SELECT i.usr_id, 'john1980'
FROM   ins1 i;

Answer 2

You can consider using a trigger. The Id column of the newly inserted row can be accessed by the name NEW.Id.

References:

1. CREATE TRIGGER documentation on PostgreSQL Manual
2. Trigger Procedures