Postgres: how to set default value to max(value) + 1

I'm glad for you that it didn't work. Please please don't do that. Use a sequence instead:

,no_af serial UNIQUE NOT NULL

If your design requires no holes in the sequence of values, then replace the design with one that allows holes.

I found a way using a trigger:

CREATE FUNCTION no_af_incrementation() RETURNS trigger AS $no_af_incrementation$
begin
    if NEW.no_af is NULL then
        NEW.no_af := (select max(no_af)+1 from af.od_af);
    end if;
    return NEW;
end;

Create a FUNCTION and use it in the DEFAULT defination.

CREATE OR REPLACE FUNCTION "public"."table_next_id"("table" text)
  RETURNS "pg_catalog"."int4" AS $BODY$
    DECLARE
    next_id int4 = 0;
    BEGIN
        EXECUTE format('select max(id)+1 from %s', "table") INTO next_id;       
    RETURN COALESCE(next_id, 1);
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TABLE "public"."merchant" (
  "id" int4 NOT NULL DEFAULT table_next_id('merchant'::text),
  "num" int4 NOT NULL,
  CONSTRAINT "merchant_pkey" PRIMARY KEY ("id")
);

I don't know if are there potential problems, especially in concurrent insertes. But it does work.

pg version 15.1