Doing either SELECT or INSERT

Question

I want to write something like a CASE statement in PostgreSQL in such a way that I want to SELECT from a table, and if nothing is found I want to INSERT into the table instead.

My example table allocated_ideas looks like this:

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

And I want to do something like this in a SQL function:

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

If this condition is true, return the idea_id. Otherwise, insert values into the table. Pseudo-code:

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)
}

Some more sample table data would be fine. And _specify_ the expected result. — jarlh, Mar 21 '21 at 21:45
There are no `CASE` or `IF` statements in SQL, only in PL/pgSQL. You could use a UNION with CTE with an `INSERT` with a `RETURNING` clause in SQL. But I wonder why the numbers in your `SELECT` and you `INSERT` differ. That would lead to the `INSERT` being constantly executed when the statement is run, unless **somewhere else** the right values are inserted. Is that really what you intend? — sticky bit, Mar 21 '21 at 22:26

Erwin Brandstetter · Answer 1 · 2021-03-22T01:23:22.887

Without concurrent write load on the table, you can simply:

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;

The data-modifying CTE ins is always executed to completion, even though it is not referenced in the final main command. (A simple SELECT wouldn't be executed without being referenced.) See:

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

If you omit target columns for the INSERT, it can silently (or noisily) break!

You did not specify to return the new idea_id from the INSERT. If you need it, you may also want to be able to tell cases apart. I added the column op for that purpose:

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;

The query can only ever return a single row, and the INSERT is executed in any case (doing nothing if sel finds a row), so we do not need LIMIT 1.

Typically, there can be concurrent write load, though, and you'd want to avoid race conditions. See:

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

DonVito · Answer 2 · 2021-03-23T17:38:47.847

Here's one way to do it using Common Table Expressions (CTEs):

WITH sel AS (
    SELECT challenge_id, user_id, idea_id, round, is_completed
    FROM allocated_ideas
    WHERE user_id = '9afbca19' AND challenge_id = '5d956daa' AND is_completed = FALSE
), ins as (
    INSERT INTO allocated_ideas (challenge_id, user_id, idea_id, round, is_completed)
    SELECT '67879', '46578', '978798', 6, FALSE
    WHERE NOT EXISTS (TABLE sel)
    RETURNING *
)
SELECT * FROM sel
UNION ALL
SELECT * FROM ins

Be aware that this only works if there are no concurrent transactions modifying the table. If you expect concurrent writes, try an INSERT with ON CONFLICT first.

Doing either SELECT or INSERT

2 Answers2