Автоматическое приращение в зависимости от значения столбца в PostgreSQL

Далее следует вопрос, Могу ли я автоматически увеличивать (verification_number) в зависимости от значения конкретного столбца (здесь business_uuid) так, чтобы verification_number увеличивался на один (1) в зависимости от самого большого числа verification_number этого business_uuid?

База данных выглядит следующим образом:
таблица: проверка

verification_id = integer, sequence (Primary Key)
business_uuid = text
verification_number = integer

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

Business_uuid является уникальным идентификатором для каждого бизнеса.

Возможно ли это?

3 ответа

  1. Не ясноverification_number, хотите ли вы сохранить таблицу, но можно создать ее во время запроса:

    select verification_id, business_uuid, 
        row_number() over(
            partition by business_uuid
            order by verification_id
        ) as verification_number
    from verification
    

    Проблема с вышеуказанным подходом заключается в том, что verification_numberизменится, если строки будут удалены

    Оконная функция

  2. Я думаю, что это плохой дизайн, но если вы действительно хотите этого, вы можете достичь этого следующим образом:

    1. Создание уникального ограничения:

      ALTER TABLE verification
         ADD CONSTRAINT verification_uuid_nr_unique
            UNIQUE (business_uuid, verification_number);
      

      Индекс, созданный этим, также сделает следующий триггер более быстрым.

    2. Создание BEFOREтриггера для изменения verification_number:

      CREATE OR REPLACE FUNCTION veritrig() RETURNS trigger
         LANGUAGE plpgsql AS
      $$BEGIN
         SELECT COALESCE(max(verification_number)+1, 1) INTO NEW.verification_number
            FROM verification
            WHERE business_uuid = NEW.business_uuid;
         RETURN NEW;
      END;$$;
      
      CREATE TRIGGER veritrig
         BEFORE INSERT OR UPDATE ON verification FOR EACH ROW
         EXECUTE PROCEDURE veritrig();
      
    3. Вставьте новые значения, как это:

      INSERT INTO verification (business_uuid) VALUES ('42');
      

      Тогда verification_numberбудет установлено так, как вы пожелаете.

    Однако существует проблема с параллелизмом.

    Если несколько сеансов пытаются вставить или обновить таблицу одновременно, вы получите такую ошибку:

    ERROR:  duplicate key value violates unique constraint "verification_uuid_nr_unique"
    DETAIL:  Key (business_uuid, verification_number)=(43, 1) already exists.
    

    Это происходит потомуSELECT, что операторы в параллельных модификациях будут видеть только текущее (зафиксированное) содержимое таблицы и могут ошибочно пытаться вставить то же verification_numberсамое для a business_uuid.

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

  3. CREATE OR REPLACE FUNCTION public.creaid(
        IN key_field text,
        IN table_mane text,
        OUT id integer)
        RETURNS integer AS
    $BODY$
        DECLARE  maxid INT;
        BEGIN
            EXECUTE 'SELECT max('||key_field||') FROM '||table_name INTO maxid;
            IF maxid IS NULL THEN
                id = 1;
            ELSE
                id = maxid + 1;
            END IF;
        END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    

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

    DEFAULT creaid('key_field'::text, 'table_name'::text)