sql - 如何插入一行并附加行号以强制执行唯一性？

Question

我有一张包含电子邮件的用户表。

users 有 user_id 和 email 列

我还有一个 user_referral_codes 列表。

user_referral_codes 有一个 user_id，它是用户 user_id 的外键。它还有一个 referral_code 列。

referral_code 将是用户电子邮件 的第一部分。

如果电子邮件是 johnsmith99@gmail.com，则 referral_code 将设置为 johnsmith。

这很好用:

`INSERT INTO flock_auth.user_referral_codes (user_id, referral_code)
VALUES($1, LEFT(LEFT($2, strpos($2, '@') - 1), 12));`

但是，可能有两个相继添加的电子邮件匹配如:

johnsmith@gmail.com 和 johnsmith@aol.com

在那种情况下，我希望第二个插入成为:

johnsmith1。

对于现有用户和推荐代码的初始 UPDATE，这工作得很好:

UPDATE auth.user_referral_codes urc
    SET referral_code = (
      LEFT(LEFT(email, strpos(email, '@') - 1), 12)
      || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
    )
    FROM (
      SELECT
        auth.users.*,
        row_number() OVER (
          PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
          ORDER BY id
        ) AS seqnum
      FROM auth.users
    ) AS u
  WHERE urc.user_id = u.id;

我已尝试修改此代码以进行插入。

INSERT INTO auth.user_referral_codes (user_id, referral_code)
  SELECT (
    $1,
    LEFT(LEFT(email, strpos(email, '@') - 1), 12)
    || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
    )
    FROM (
      SELECT
        auth.users.*,
        row_number() OVER (
          PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
          ORDER BY id
        ) AS seqnum
      FROM auth.users
    ) AS u
  WHERE $1 = u.id;

错误返回为:INSERT has more target columns than expressions

我怎样才能让它工作？

Answer 1

在不进入你的代码的情况下，理论上我会做这样的事情:

with parsed as (
  select
    email,
    left (split_part (email, '@', 1), 12) as user_id,
    row_number() over
        (partition by left (split_part (email, '@', 1), 12) order by id) - 1 as rn
  from auth.users
)
select
  email,
  case
    when rn > 0 then user_id || rn
    else user_id
  end as user_id
from parsed

你可以不用 CTE 就可以逃脱，但它确实让事情变得更干净了。