Decrement integer in postgres only if larger than a minimum

Question

I have a statement like this, that I use to decrement a counter:

UPDATE stats SET counter = counter - 1 WHERE id = 24235;

The problem is, that I never want the counter to go below 1. How do I execute an update statement, that will only decrement down till 1?

PG version 9.3

Answer 1

UPDATE stats
SET counter = counter - 1
WHERE id = 24235
  AND counter > 1;

If the decrementing amount varies, you have to make sure to pass in the same value in both places.

UPDATE stats
SET counter = counter - ?
WHERE id = ?
  AND counter > ?;

Which you'd call with some pseudocode like sql.execute(decrement, id, decrement)

If the counter is never supposed to go below 1, I would add a constraint on that field. counter INTEGER CHECK (counter > 0)

Answer 2

Another way...

UPDATE stats SET counter = (
  CASE WHEN counter > 1 
  THEN (counter - 1) 
  ELSE 1 END
) WHERE id = 24235;