CREATE TABLE Users(
id SERIAL PRIMARY KEY,name TEXT UNIQUE
);
以下操作的正确单一查询插入是什么：
给定用户名,插入新记录并返回新ID.但如果名称已存在,则只返回id.
我知道Postgresql 9.5中的新语法对于ON CONFLICT(列)DO UPDATE / NOTHING,但我无法弄清楚如果有的话,它可以提供帮助,因为我需要返回id.
似乎RETURNING id和ON CONFLICT不属于一起.
解决方法
这是手册中解释您情况的关键声明：
The Syntax of the
RETURNINGlist is identical to that of the output
list ofSELECT. Only rows that were successfully inserted or updated
will be returned. For example,if a row was locked but not updated
because anON CONFLICT DO UPDATE ... WHEREclause condition was not
satisfied,the row will not be returned.
大胆强调我的.
要插入一行：
WITH ins AS (
INSERT INTO users(name)
VALUES ('new_usr_name') -- input value
ON CONFLICT(name) DO UPDATE
SET name = name WHERE FALSE -- never executed,just to lock row
RETURNING users.id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM users -- 2nd SELECT never executed if INSERT successful
WHERE name = 'new_usr_name' -- input value a 2nd time
LIMIT 1;
或者包装成一个函数,只提供一次新名称.就像在这里演示的那样(也考虑LIMIT 1的解释)：
> Is SELECT or INSERT in a function prone to race conditions?
可能的竞争：并发事务可能会更改/删除INSERT尝试和SELECT之间的现有行.极不可能,但可能.
如果您没有(可能的)并发写访问权限(或者只是不关心),请简化：
... ON CONFLICT(name) DO NOTHING ...
要插入一组行：
> How to include excluded rows in RETURNING from INSERT … ON CONFLICT
