Suppose I have a table of the following structure:

```
CREATE TABLE test(
  id SERIAL,
  type VARCHAR(10),
  sub_id INTEGER,
  UNIQUE (type, sub_id)
)

```

where I want the "sub\_id" column to be a counter within a specific "type". Example:

```
id  |  type  | sub_id
---------------------
1   |  'foo' | 1
--------------------
2   |  'foo' | 2
--------------------
3   |  'foo' | 3
--------------------
4   |  'bar' | 1
--------------------
5   |  'bar' | 2

```

To insert a new row I thought of using the following query:

```
INSERT INTO test(type,sub_id)
SELECT 'foo', MAX(sub_id)+1
FROM test
WHERE type='foo'

```

But then I discovered that this query is vulnerable to race conditions.
What is a proper way to keep sub counters within types?