sql - 冲突时增加文本列 "cool name"--> "cool name 1"

Question

我在表中有一个唯一的列 display_name。插入新行时，我想执行以下操作 ON CONFLICT (display_name):将数字附加到 display_name 值，使其现在是唯一的。

例如在 postgres 10 中，

CREATE TABLE some_table (
  id SERIAL NOT NULL PRIMARY KEY,
  display_name text UNIQUE
);

INSERT INTO some_table (display_name) VALUES ('cool name');

INSERT INTO some_table (display_name) VALUES ('cool name')
  ON CONFLICT (display_name) ....
  -- 'cool name' is a duplicate here, can I somehow increment a suffix
  -- here until it is a unique value (like 'cool name 1')?

Answer 1

ON CONFLICT DO UPDATE 将更新现有行，而不是要插入的新行。您需要做的是动态选择要插入的值，使其不存在:

INSERT INTO some_table (display_name)
SELECT name
FROM generate_series(0, 1000) num,
LATERAL (SELECT 'cool name' || CASE WHEN num = 0 THEN '' ELSE ' ' || num END AS name) AS t
WHERE NOT EXISTS (SELECT 1 FROM some_table WHERE display_name = name)
ORDER BY num
LIMIT 1;

^{( online demo )}
这将尝试最多 1000 个后缀，直到它放弃。