166

I'm a little new to Postgres. I want to take a value (which is an integer) in a field in a Postgres table and increment it by one. For example, if the table 'totals' had 2 columns, 'name' and 'total', and Bill had a total of 203, what would be the SQL statement I'd use in order to move Bill's total to 204?

funnydman
  • 9,083
  • 4
  • 40
  • 55
greatwitenorth
  • 2,241
  • 2
  • 19
  • 21

1 Answers1

316
UPDATE totals 
   SET total = total + 1
WHERE name = 'bill';

If you want to make sure the current value is indeed 203 (and not accidently increase it again) you can also add another condition:

UPDATE totals 
   SET total = total + 1
WHERE name = 'bill'
  AND total = 203;
  • 3
    I was trying to increment **non-integer** datatype and getting:`ERROR: operator does not exist: character varying + integer LINE 2: SET total = total + 1` Solved by casting the value as integer like this `SET total = total::int + 1` – Stew-au Oct 31 '12 at 03:05
  • 50
    @Stew-au: Do ***not*** store numbers in varchar columns. That will give you trouble in the long run. Use an integer (or bigint or whatever is suitable) but do not use a character datatype. –  Oct 31 '12 at 07:12
  • 12
    Is this statement atomic or would I need pessimistic lock the table for writes first? (My fear is that in between of assigning total and the fetching of total for total + 1 some writing has been done to the table.) – miho Sep 17 '15 at 14:47
  • 20
    A single statement is always atomic in a relational database. However running the update will not prevent others from _reading_ the old values until your transaction is committed –  Sep 17 '15 at 14:49
  • And how it'll be for upsert? Cauz if I try to use `total = total + EXCLUDED.total` it show error: **ERROR: column reference "total" is ambiguous at character XYZ**. – Lucas Jul 19 '17 at 19:54
  • How can I insert it if the key is not exist? – petertc Jan 04 '18 at 12:29
  • 4
    For upsert see https://dba.stackexchange.com/questions/161127/column-reference-is-ambiguous-when-upserting-element-into-table – iElectric Sep 02 '18 at 17:07