sql - 如何在向 PostgreSQL 中的表 A 中插入时有条件地在表 B 中插入一条记录？

Question

给定以下结构:

表 A(别名):

user_id | alias 
---------------
   1      john
   2      peter

user_id 引用 users 中的 id。

表 B(用户):

  id | password_hash | ...
---------------------------
   1        ...        ...
   2        ...        ...

(想法是用户可以有多个别名，所有这些别名都指向同一个主用户帐户记录)

我想做以下操作:给定一个别名，密码，...记录:

• 如果aliases中存在alias，更新users中对应的password
• 如果 alias 不存在，在 users 中用给定的密码创建一个新用户，并在 aliases 中插入一行指向这个新用户记录。

我如何在 Postgres 中的单个查询中执行此操作？

类似的东西

WITH (
  INSERT INTO users(id, password, ...) VALUES(DEFAULT, password, ...) RETURNING id
)
INSERT INTO aliases(user_id, alias) VALUES(id, alias)
  ON CONFLICT {delete the temp row in users and update the one with the 
               known user_id instead}

Answer 1

这假设 users_id_seq 是用于 users.id 的序列并且 aliases.alias 有一个 UNIQUE 约束:

WITH a AS (INSERT INTO aliases (user_id, alias)
              VALUES (nextval('users_id_seq'), p_alias)
           ON CONFLICT (alias)
              /* this does nothing, but is needed for RETURNING */
              DO UPDATE
                 SET user_id = aliases.user_id
           RETURNING user_id
          )
INSERT INTO users (id, password_hash, ...)
   SELECT user_id, p_password, ...
      FROM a
ON CONFLICT (id)
   DO UPDATE
      SET password_hash = EXCLUDED.password_hash;