sql - 执行 SELECT 或 INSERT

Question

我想写一些类似 CASE 的东西PostgreSQL 中的语句以我想要的方式 SELECT从表中，如果没有找到我想要 INSERT改为进入 table 。
我的示例表 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)
}

Answer 1

无并发写入负载 在 table 上，您可以简单地:

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;

数据修改 CTE ins总是执行到完成，即使它没有在最终的主命令中引用。 (一个简单的 SELECT 在没有被引用的情况下不会被执行。)见:

Delete parent if it's not referenced by any other child

如果您省略了 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 .
通常，可以有 并发写入负载 ，但是，您希望避免竞争条件。看:

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