执行 SELECT 或 INSERT

我想写一些类似的东西CASE以我想要的方式在 PostgreSQL 中声明SELECT从表中，如果什么也没找到，我想INSERT改为放入表中。

我的示例表allocated_ideas看起来像这样：

challenge_id | user_id    | idea_id        | round | is_completed
-------------+------------+----------------+-------+-------------
 '5d956daa'  | '9afbca19' | '3798826ae522' |   5   | false

我想在 SQL 函数中做这样的事情：

SELECT EXISTS (
SELECT 1 from allocated_ideas ai
where ai.user_id = '9afbca19' 
and ai.challenge_id = '5d956daa'
    and ai.is_completed is false

如果此条件为真，则返回idea_id。否则，将值插入表中。伪代码：

if (above query is true) {
    SELECT idea_id from allocated_ideas ai 
    where ai.user_id = '9afbca19' 
    and ai.challenge_id = '5d956daa'
        and ai.is_completed is false 
} else {
  INSERT VALUES INTO allocated_ideas('67879', '46578', '978798', 6, false)
}

无并发写入负载在桌子上，您可以简单地：

WITH sel AS (
   SELECT idea_id
   FROM   allocated_ideas
   WHERE  user_id = '9afbca19' 
   AND    challenge_id = '5d956daa'
   AND    is_completed IS false 
   )
, ins AS (
                             -- spell out target columns!
   INSERT INTO allocated_ideas (challenge_id, user_id, idea_id, round, is_completed)
   SELECT '67879', '46578', '978798', 6, false
   WHERE  NOT EXISTS (TABLE sel)               -- only when sel is empty!
   -- RETURNING idea_id                        -- unused
)
TABLE sel;

数据修改 CTEins始终执行完成，即使最终主命令中没有引用它。 （一个简单的SELECT如果没有被引用就不会被执行。）请参阅：

• 如果父级未被任何其他子级引用，则删除父级 https://stackoverflow.com/questions/15809463/delete-parent-if-its-not-referenced-by-any-other-child/15810159#15810159

如果您省略目标列INSERT，它可以无声地（或大声地）破裂！

您没有指定返回新的idea_id来自INSERT。如果您需要它，您可能还希望能够区分案例。我添加了专栏op为了这个目的：

WITH sel AS (
   -- like above
, ins AS (
   -- like above
   RETURNING idea_id                        -- used now
   )
SELECT 'sel' AS op, * FROM sel
UNION ALL
SELECT 'ins' AS op, * FROM ins;

该查询只能返回一行，并且INSERT在任何情况下都会执行（如果sel找到一行），所以我们不需要LIMIT 1.

通常，可以有并发写入负载但是，您希望避免竞争条件。看：

• 函数中的 SELECT 或 INSERT 是否容易出现竞争条件？ https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324