Auto increment depending on value of column in PostgreSQL

I think that this is a bad design, but if you really want that, you can achieve it as follows:

1. Create a unique constraint:

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

   The index created by this will also make the following trigger function faster.

2. Create a BEFORE trigger to modify 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 new values like this:

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

   Then verification_number will be set as you desire.

There is a problem with concurrency, however.

If several sessions try to insert or update the table concurrently, you will get an error like this:

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

That is because the SELECT statements in concurrent modifications will only see the current (committed) table contents and may erroneously try to insert the same verification_number for a business_uuid.

There is no way to avoid that short of locking the table. But if you receive such an error, you can simply retry the transaction, and odds are that it will work the next time.

It is not clear if you want the verification_number to be saved to the table but it is possible to create it at query time:

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

The problem with the above approach is that the verification_number will change if rows are deleted

Window functions

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;

Then, you can call that function as a default value of every key value of every table of your database, as follows:

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