I'm a beginner at PostgreSQL, How do I create a simple counter which I can reset?

Question

Hello!

I want to achieve the following:

ID | Counter
------------
 0 | 343
 1 | 8344

Now say that I want to update counter for ID 1,,, what is the easiest way to do it? Do I use sequences? do I simply read the value and update? Is there any special type for it?

I was thinking about using sequence but then I have to create one for each ID (which potentially can be over a 1000. I will also face the problem that I don't know how many sequences I will need so I would have to check if there is a sequence for that ID and etc... and I don't want that.

Assume that the numbers are users belonging to a certain group, then an alternative I was thinking about was to enter a row for each count and when I want to get the number I perform a select group by the id or something and get the numbers of rows.

EDIT: Clarification I recieve a list of users in a csv that my program handles several times a day (new csv several times a day). Then depending on if the user has has sent a message today (for example) I increment the counter for the group in which this user belongs to. Now at a certain point I want to extract the groups (which can be dynamic, it depends on what I got during the day) and get the number I incremented and reset it. Hopefully this explains it more :D

Thanks for the help so far, I will experiment :D

What do you think?

Answer 1

It sounds like what you need is to create a view exactly along the lines of:

select id, count(users) from foo group by id

Answer 2

UPDATE Table SET Counter=Counter+1 WHERE ID=:ID;

(where 'Table' is of course the table with the counter, and the parameter ':ID' is the id of the counter)

Sequences are mainly used for auto-generating ID values sequentially and can have gaps. The update statement above is atomic, as update is an atomic action. However it's not said that if you issue a select right afterwards the counter is still the same value. If you want that, you need sequences but then you run the risk of having gaps.

So it might be necessary for answerers here to know what the purpose of the counters is.

Answer 3

This would seem simple enough.

Increment

UPDATE Table SET Counter = Counter + 1 WHERE ID = 1

Reset

UPDATE Table SET Counter = 0 WHERE ID = 1