0

Suppose we have a simple update query:

UPDATE foo
SET
  bar=$1
WHERE id=$2

where bar=$1 is generated based on user's input and it might be null, e.g.

UPDATE foo
SET
  ${assignmentList}
WHERE id=$2

How do I make this update operation into a no-op in case assignmentList variable is empty?

I have tried using 1=1, i.e.

UPDATE foo
SET
  1=1
WHERE id=$2

but this gives a syntax error.

6
  • WHERE id=$2 AND (${assignmentList}) IS NOT NULL. Or WHERE id=$2 AND LENGTH('${assignmentList}')>0. Or something similar...
    – Akina
    Commented Mar 26, 2019 at 18:17
  • 3
    I cannot understand why you cannot to check ${assignmentList} on the application level, and do not execute the query at all when assignmentList it is empty.
    – Akina
    Commented Mar 26, 2019 at 18:21
  • 1
    Context: github.com/gajus/slonik/issues/46 @Akina I think that the best API is for the generator to throw an error if empty assignment list is provided and make the user guard to make sure that does not happen/ let him not to execute the query. I was checking if there is a generic no-op pattern, though.
    – Gajus
    Commented Mar 26, 2019 at 18:23
  • I could do UPDATE foo SET id=id WHERE id=$2 or any other known column, but that would actually perform an update of the row.
    – Gajus
    Commented Mar 26, 2019 at 18:24
  • 2
    Please confirm that you assemble that variable in a way in cannot be something like col = 1; DROP DATABASE this;-- Commented Apr 18, 2019 at 8:36

2 Answers 2

2

It is only slightly more code to skip the UPDATE if there is nothing to do. And the code is much cleaner. And less kludgy.

0

While Rick's answer addresses the root cause here, I would like to answer the more general question "How to write a no-op UPDATE query?"

You can write an UPDATE that does not update any row by providing a WHERE clause that is never true.

UPDATE foo
SET bar = 'value'
WHERE 1 <> 1
;
1
  • 1
    I don't see that it's possible in Postgres. I have a "reasonable" requirement for doing so; I want an update trigger to fire on a few thousand rows and do what it needs to do. So, I want a no-op update where I don't specify any more info just so that it causes the trigger to fire. I'll just workaround... maybe like this: stackoverflow.com/questions/53403464/…
    – David B
    Commented Feb 15, 2022 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.