在运行"冲突时"命令时控制表的主键值

Question

得到一个表，我需要填充数据，同时摆脱重复项。我用的是ON CONFLICT ... DO NOTHING。问题是，当表有一个auto_increment primary_key字段-我们称之为id -时，即使没有插入重复项，该字段似乎也会继续增加，因为id字段值远远高于已成功插入的记录数。

不幸的是，SQL Fiddle目前不支持postgresql 9.5，所以我将复制粘贴下面的代码。

CREATE TABLE table_one
(
    id    serial primary key,
    col_foo        VARCHAR(40) not null unique,
    col_bar        VARCHAR(20)
);
INSERT into table_one (col_foo, col_bar) 
VALUES ('1a', '1b'), ('2a', '2b'), ('1a', '2b'),('1a', Null), ('3a', '1b'), ('4a', '2b'), ('1a', '2b'),('1a', Null) 
ON CONFLICT (col_foo) DO NOTHING;

如果你在postgresql 9.5上运行，你会发现最终的主键是6，而只有4条记录。是否有可能确保如果6条记录中的4条被成功插入，那么最大/最后id字段的值应该为4?

在我当前的案例中，我正在处理一个大数据集，其中插入了120万条记录，但最后一条记录的id值为6200万。

Answer 1

您当然可以使用临时表来捕获&抑制重复:

CREATE TABLE table_one
(
    id    serial primary key,
    col_foo        VARCHAR(40) not null unique,
    col_bar        VARCHAR(20)
);
CREATE TEMP TABLE temp_one
(
    id    serial primary key, -- dont actually need this
    col_foo        VARCHAR(40) not null unique,
    col_bar        VARCHAR(20)
);
INSERT into temp_one (col_foo, col_bar)
VALUES ('1a', '1b'), ('2a', '2b'), ('1a', '2b'),('1a', Null), ('3a', '1b'), ('4a', '2b'), ('1a', '2b'),('1a', Null)
ON CONFLICT (col_foo) DO NOTHING
        ;
INSERT into table_one (col_foo, col_bar)
SELECT col_foo, col_bar FROM temp_one
ON CONFLICT (col_foo) DO NOTHING -- wont need this
         -- (except for suppressing already-existing duplicates)
        ;
SELECT * FROM temp_one;
SELECT * FROM table_one;

Answer 2

你不能真正改变ON CONFLICT的行为。它只允许更新有冲突的行，而不是创建新的行。

您可以重置序列并在之后重新分配id，但是:

SELECT setval('table_one_id_seq', 1);
UPDATE table_one SET id = nextval('table_one_id_seq');

当然，您不应该依赖最后一个ID来获取行数。如果您担心id耗尽，请使用bigserial而不是serial。