How to increment value in PostgreSQL?

Question

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?

user330315 · Accepted Answer · 2012-04-19 17:20:23Z

345

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;

answered Apr 19, 2012 at 17:20

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

Stew-au Over a year ago

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

user330315 Over a year ago

@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.

miho Over a year ago

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.)

user330315 Over a year ago

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

iElectric Over a year ago

For upsert see dba.stackexchange.com/questions/161127/…

|

Collectives™ on Stack Overflow

How to increment value in PostgreSQL?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related