postgresql is incrementing an update by 2 ?

Question

I'm migrating our model to postgresql for the FTS and data integrity

update myschema.counters set counter_count= (counter_count+1) where counter_id =?

Works as expected in mysql, however in postgres it is incrementing by 2 each time? It is simple int field I believe, I don't have anything special going on.

Answer 1

You should use a sequence to populate the value.

CREATE SEQUENCE counter_seq START 1;

UPDATE myschema.counters 
   SET counter_count = NEXTVAL('counter_seq')
 WHERE counter_id = ?