Update from selection, otherwise revert to other selection if empty in PostgreSQL-CodePudding

I have the following query being executed:

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
    (
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=false
     ORDER BY RANDOM() LIMIT 1
     FOR UPDATE SKIP LOCKED
    )
  AND pending=false AND closed=false AND gpu=false
;

However I would like it to attempt to fetch a job using the same query, but requiring csv=true, and if there are no jobs matching that query, it should revert to using the current selection (as used above inside the brackets).

Here would be the selection with csv=true:

    (
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=false AND csv=true
     ORDER BY RANDOM() LIMIT 1
     FOR UPDATE SKIP LOCKED
    )
  AND pending=false AND closed=false AND gpu=false AND csv=true

How could I achieve this, preferably only executing the second query if it needs to?

Thanks in advance.

CodePudding user response：

There are multiple ways to achieve it. 1 of them is -

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
    (
     SELECT "number"
       FROM (SELECT "number", 1 PRIORITY
               FROM "job" 
              WHERE pending=false AND closed=false AND gpu=false AND csv=true
              ORDER BY RANDOM() LIMIT 1
                FOR UPDATE SKIP LOCKED
              UNION ALL
             SELECT "number", 2
               FROM "job" 
              WHERE pending=false AND closed=false AND gpu=false
              ORDER BY RANDOM() LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
       ORDER BY PRIORITY
       LIMIT 1
    )
  AND pending=false
  AND closed=false
  AND gpu=false;

Basically the idea is give the first priority to "CSV" records and 2nd to other records anduse LIMIT 1 to restrict it to 1 record only If it found CSV records then it will update that els the record matching 2nd criteria would be updated.

CodePudding user response：

Order by the optional criteria. I assume csv is a boolean type and NULL doesn't meet the criteria.

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
    (
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=false
     ORDER BY coalesce(csv,false) DESC, RANDOM() 
     LIMIT 1
     FOR UPDATE SKIP LOCKED
    )
  AND pending=false AND closed=false AND gpu=false