Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
  test_id serial PRIMARY KEY
, test text
);

-- tailored function for "test" table
CREATE OR REPLACE FUNCTION f_test_test_id_seq(OUT nextfree bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      SELECT INTO nextfree  val
      FROM   nextval('test_test_id_seq'::regclass) val  -- use actual name of sequence
      WHERE  NOT EXISTS (SELECT FROM test WHERE test_id = val);

      EXIT WHEN FOUND;
   END LOOP; 
END
$func$;

ALTER TABLE test ALTER COLUMN test_id SET DEFAULT f_test_test_id_seq();

-- generic function for multiple use
CREATE OR REPLACE FUNCTION f_nextfree(_tbl regclass
                                     , _col text
                                     , _seq regclass
                                     , OUT nextfree bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      EXECUTE '
      SELECT val FROM nextval($1) val WHERE NOT EXISTS (
         SELECT FROM ' || _tbl || ' WHERE ' || quote_ident(_col) || ' = val)'
      INTO  nextfree
      USING _seq;

      EXIT WHEN nextfree IS NOT NULL;
   END LOOP; 
END
$func$;

CREATE TABLE test2 (test2_id serial PRIMARY KEY, test2 text);

ALTER TABLE test2 ALTER COLUMN test2_id
SET DEFAULT f_nextfree('test2', 'test2_id', 'test2_test2_id_seq');

CREATE TABLE

CREATE FUNCTION

ALTER TABLE

CREATE FUNCTION

CREATE TABLE

ALTER TABLE

-- test
INSERT INTO test VALUES
  (2,'2x')
, (3,'3x')
, (6,'6x')
, (7,'7x');

INSERT INTO test (test) VALUES ('aa');
INSERT INTO test (test) VALUES ('bb');
INSERT INTO test (test) VALUES
  ('cc')
, ('dd')
;

INSERT 0 4

INSERT 0 1

INSERT 0 1

INSERT 0 2

-- test2
INSERT INTO test2 VALUES
  (2,'2x')
, (3,'3x')
, (6,'6x')
, (7,'7x')
;

INSERT INTO test2 (test2) VALUES ('aa');
INSERT INTO test2 (test2) VALUES ('bb');
INSERT INTO test2 (test2) VALUES
  ('cc')
, ('dd')
;

INSERT 0 4

INSERT 0 1

INSERT 0 1

INSERT 0 2

-- show result
TABLE test ORDER BY test_id;
TABLE test2 ORDER BY test2_id;

test_id
test

1
aa

2
2x

3
3x

4
bb

5
cc

6
6x

7
7x

8
dd

SELECT 8

test2_id
test2

1
aa

2
2x

3
3x

4
bb

5
cc

6
6x

7
7x

8
dd

SELECT 8