在冲突“酷名”-->“酷名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子句中插入行(只能更新现有行)，因此不能使用该构造。使用plpgsql，例如：

do $$
declare new_str text := 'cool name';
begin   
    insert into some_table (display_name)
    values (new_str);
exception when unique_violation then
    insert into some_table (display_name)
    select format('%s %s',
        new_str,
        (select coalesce(nullif(regexp_replace(display_name, '^.*?(\d+)$', '\1'), display_name), '0')::int+ 1
        from some_table
        where left(display_name, length(new_str)) = new_str
        order by 1 desc
        limit 1)
    );
end $$;

db<>fiddle.现场演示