从条件INSERT获取Id [英] Get Id from a conditional INSERT

问题描述

对于这样的表：

  CREATE TABLE用户（
 id SERIAL PRIMARY KEY，
 name TEXT UNIQUE 
）; 
  

对于以下操作，将是正确的单查询插入：

给定用户 name ，插入一个新记录并返回新的 id 。但是如果 name 已经存在，只需返回 id 。

我知道PostgreSQL 9.5中对于 ON CONFLICT（column）DO UPDATE / NOTHING 的新语法，但我不知道如果，它可以帮助，因为我需要返回 id 。

似乎 RETURNING id 和 ON CONFLICT do不属于一起。

解决方案

UPSERT实施是非常复杂的，对于并发写访问是安全的。查看在初始开发期间用作日志的此Postgres Wiki 。 Postgres黑客决定不在Postgres 9.5的第一个版本的 RETURNING 子句中包含被排除的行。

这是手册中关键的语句来解释你的情况：

RETURNING 列表的语法与输出的语法相同
SELECT 。 只有成功插入或更新的行会被返回
。例如，如果行被锁定但未更新
，因为 ON CONFLICT DO UPDATE ... WHERE 条件不满足
，不会返回该行。

< b $ b

  WITH ins AS（
 INSERT INTO users（name）
 VALUES（'new_usr_name'） -  input value 
 ON CONFLICT名称）DO UPDATE 
 SET name = name WHERE FALSE  - 从未执行，只是锁定行
 RETURNING users.id 
）
 SELECT id FROM ins 
 UNION ALL 
 SELECT id FROM users  - 第二次SELECT INSERT成功时不会执行
 WHERE name ='new_usr_name' - 第二次输入值
 LIMIT 1; 
  

或换行到一个函数，只提供一次新的名称​​一次。像这里展示的（也考虑 LIMIT 1 的解释）：

• 函数中的SELECT或INSERT容易出错

可能的种族：并发事务可能会更改/删除 INSERT 尝试和 SELECT 。

如果您没有（可能的）并发写入权限（或只是不关心），请简化：

  ... 
 ON CONFLICT（name）DO NOTHING 
 ... 
  

要插入 行 ：

$ b b

• 如何在ON CONFLICT中插入此PostgresSQL？

For a table like this one:

CREATE TABLE Users(
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

What would be the correct one-query insert for the following operation:

Given a user name, insert a new record and return the new id. But if the name already exists, just return the id.

I am aware of the new syntax within PostgreSQL 9.5 for ON CONFLICT(column) DO UPDATE/NOTHING, but I can't figure out how, if at all, it can help, given that I need the id to be returned.

It seems that RETURNING id and ON CONFLICT do not belong together.

解决方案

The UPSERT implementation is hugely complex to be safe against concurrent write access. Take a look at this Postgres Wiki that served as log during initial development. The Postgres hackers decided not to include "excluded" rows in the RETURNING clause for the first release in Postgres 9.5. They might build something in for the next release.

This is the crucial statement in the manual to explain your situation:

The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ON CONFLICT DO UPDATE ... WHERE clause condition was not satisfied, the row will not be returned.

Bold emphasis mine.

For a single row to insert:

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;

Or wrap into a function, to only provide the new name once. Like demonstrated here (also consider the explanation for LIMIT 1):

• Is SELECT or INSERT in a function prone to race conditions?

The possible race: a concurrent transaction might change / remove the existing row between the INSERT attempt and the SELECT. Highly unlikely, but possible.

If you don't have (possible) concurrent write access (or just don't care), simplify:

...
ON     CONFLICT(name) DO NOTHING
...

To insert a set of rows:

• How do I insert into this PostgresSQL with ON CONFLICT?

这篇关于从条件INSERT获取Id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！