Как в PostgreSQL сделать поле с автоматическим инкриментом по условию?

Question

[Rukis]

Когда то уже спрашивал на тостере подобное для mysql: Mysql как сделать поле альтернативной нумерации?

В чем суть, есть таблица с записями, у каждой записи есть id и category и нужно иметь порядковую нумерацию отдельную для каждой категории. То есть, третье поле alt_id в которое должен записываться порядковый номер записи в категории. (чтобы порядок этой нумерации не сбивался при удалении записей, и чтобы можно было всегда получить одним запросом одну запись и ее порядковый номер в категории. Категории у записей не меняются)

Этого можно было бы добиться, например делая перед каждой вставкой запрос

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

увеличивать значение на единицу и писать в таблицу. Но, во первых, после этого запроса может успеть выполнится другой запрос на вставку и счетчик будет неверным, во вторых хотелось бы гарантировать увеличение счетчика на уровне БД.

Для mysql мне предложили использовать триггер. Хотелось бы узнать предоставляет ли PostgreSQL какие нибудь еще возможности для решения этой задачи и какой вариант для него был бы лучшим?

Answer 1

[Константин Цветков]

Но, во первых, после этого запроса может успеть выполнится другой запрос на вставку и счетчик будет неверным

Для этого есть блокировка.
Уточнение: решение прикладной задачи по нумерации.

Comments

[Rukis]

То есть, вы решали бы задачу на стороне языка, в два запроса с блокировкой? (просто уточнение, я тоже рассматриваю такой вариант, но интересует нет ли подходящей фичи у СУБД)

Answer 2

[zwerg44]

Думаю, для этой задачи, я бы не хранил порядковый номер в отдельной колонке, а рассчитывал бы на лету через оконную функцию.

Как-нибудь так:

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

Или создал бы VIEW или MATERIALIZED VIEW для подобного селекта.

Comments

[Rukis]

чтобы порядок этой нумерации не сбивался при удалении записей, и чтобы можно было всегда получить одним запросом одну запись и ее порядковый номер в категории

что будет при удалении одной из записей? Нумерация разве не съедет?

[zwerg44]

Это просто нумерация.
При удалении из середины категории - нумерация последующих записей сдвинется на 1 вверх.

По исходному вопросу я подумал, что вы собираетесь - пересчитывать содержимое колонки alt_id, после удаления из середины категории.

[Rukis]

zwerg44, нет, я имел ввиду, что номер должен привязываться записи и уже не меняться