How to make a field with automatic increment by condition in PostgreSQL?

Question

PostgreSQL

Once I already asked something similar for mysql on the toaster: Mysql how to make an alternative numbering field?
What is the point, there is a table with entries, each entry has an id and a category, and you need to have a separate serial numbering for each category. That is, the third field alt_id in which the serial number of the record in the category should be written. (so that the order of this numbering does not go astray when deleting records, and so that you can always get one record and its serial number in the category with one request. Categories for records do not change)
This could be achieved, for example, by making a query before each insertion

select max(alt_id) from table where category = 'blabla';

increase the value by one and write to the table. But, firstly, after this request, another insert request may be executed and the counter will be incorrect, and secondly, I would like to guarantee the counter increase at the database level.
For mysql, I was suggested to use a trigger. I would like to know if PostgreSQL provides any other options for solving this problem and what would be the best option for it?

Answer 1

But, firstly, after this request, another insert request may have time to execute and the counter will be incorrect

There is a lock for this.
Refinement: solution of an applied problem by numbering .

Answer 2

I think for this task, I would not store the serial number in a separate column, but would calculate it on the fly through the window function.
Something like this:

WITH tmp AS (
  SELECT
    id,
    name,
    category,
    ROW_NUMBER () OVER (
      PARTITION BY category 
      ORDER BY name, id
    ) AS alt_id
  FROM tablename
)
SELECT *
FROM tmp t
WHERE t.id = 123

Or would create VIEW or MATERIALIZED VIEW for similar select.