Increment a value in Postgres

postgresql increment

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?

a_horse_with_no_name

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;

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

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

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

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

Increment a value in Postgres

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US