Figure this table:
I am looking for a request that will either insert or update depending the context. The idea is to simulate cyclic pointers in a postgres table.
Giving to the request the parameters cat=cat1 and value=v4. (Rolling update case, because there is already 3 records in cat1) the request should:
- search for cat=cat1 records (3 matches)
- 3 is the max count of record of each category (hardcoded), so
- update record having id=1 with value=v4 and updatedAt=now() because it has the older createdAt value
Giving to the request the parameters cat=cat2 and value=v3, (Insert case, max count of cat=cat2 records is not reached) the request should:
- search for cat=cat2 records (2 matches)
- 2 is under the max count of record of each category (hardcoded), so
- insert a record having id=6, cat=cat2, value=v3
It sounds weird to have one request performing INSERT or UPDATE. The need is to have cyclic pointers, "one request and no transaction" would be nice but maybe impossible?