Postgres upstart而不增加序列号

Question

考虑下表：

CREATE TABLE key_phrase(
id SERIAL PRIMARY KEY NOT NULL,
body TEXT UNIQUE
)

我想做以下事情：

1. 如果不存在给定的body，则创建一个记录
2. 返回新创建记录的id，如果没有创建新记录，则返回现有记录的id
3. 确保序列号id在冲突时不会递增

我尝试了几种方法，最简单的包括DO NOTHING:的基本用法

INSERT INTO key_phrase(body) VALUES ('example') ON CONFLICT DO NOTHING RETURNING id

但是，如果创建了新记录，这只会返回id。

我也尝试过以下方法：

WITH ins AS (
INSERT INTO key_phrase (body)
VALUES (:phrase)
ON     CONFLICT (body) DO UPDATE
SET    body = NULL
WHERE  FALSE     
RETURNING id
)
SELECT id FROM ins
UNION  ALL
SELECT id FROM key_phrase
WHERE  body = :phrase
LIMIT  1;

这将返回新创建记录的id或现有记录的id

那么，如何执行满足前面提到的3个要求的条件插入(upstart(呢？

Answer 1

我怀疑您正在寻找类似以下的东西：

with 
data as (select :phrase as body),
ins as (
insert into key_phrase (body)
select body 
from data d
where not exists (select 1 from key_phrase kp where kp.body = d.body)
returning id
)
select id from ins
union all
select kp.id 
from key_phrase kp
inner join data d on d.body = kp.body

与原始代码的主要区别在于，它使用not exists跳过已插入的短语，而不是on conflict。我将参数的声明移到了CTE中，以使事情更容易遵循，但不一定非得这样，我们可以这样做：

with 
ins as (
insert into key_phrase (body)
select body 
from (values(:phrase)) d(body)
where not exists (select 1 from key_phrase where body = :phrase)
returning id
)
select id from ins
union all
select kp.id from key_phrase where body = :phrase

不使用on conflict将减少被刻录的序列数。然而，应该强调的是，没有办法保证序列始终是连续的。由于其他原因可能存在差距。这是经过设计的；连载的目的是保证唯一性，而不是"唯一性"；顺序性"；。如果你真的想要一个没有孔的自动增量，可以考虑row_number()和一个视图。