Increment a value in Postgres

postgresql increment
greatwitenorth picture greatwitenorth · Apr 19, 2012 · Viewed 111.8k times · Source

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?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Apr 19, 2012 
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;

Related questions

Incrementing a number in a loop in plpgsql

I couldn't find this immediately from the examples. I want to increment a variable in a loop, in a function. For instance: DECLARE iterator float4; BEGIN iterator = 1; while iterator < 999 ..... iterator ++; END; How would this be done? I was looking …

Read More
PostgreSQL: Show tables in PostgreSQL

What's the equivalent to show tables (from MySQL) in PostgreSQL?

Read More
PostgreSQL: How to change PostgreSQL user password?

How do I change the password for PostgreSQL user?

Read More