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

我有一张包含电子邮件的users表格。
users同时具有user_id和email列
我还有一个user_referral_codes列表。
user_referral_codes有一个user_id是用户user_id上的外键。它还有一个referral_code列。
referral_code将是用户email的第一部分。
如果电子邮件[email protected]，则referral_code将设置为johnsmith。
这很管用：

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

但是，可能会有两封电子邮件相继添加，如：
[email protected]和[email protected]
在这种情况下，我希望第二次插入变成：
johnsmith1。
在现有用户和推荐代码的初始版本中，这样做很好：

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;

错误返回为：UPDATE
我该怎么做？

最佳答案

如果不进入你的代码，我会做这样的事情：

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你可以逃走，但它确实让事情变得更干净了。

关于sql - 如何插入行并附加在行号上以强制唯一性？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52113677/