Postgres function create unique string and insert into table

Question

I have a table in Postgres that contains columns for a userid and an external id that will be used in API calls to an external service. I have to create the external id on my side, validate that is unique, and store it in PG before calling the external API. This article comes close to what I want: How can I generate a unique string per record in a table in Postgres? However, there's the possibility of conflict if two concurrent calls generate the same id. What I would like to do is have a loop that generates a random string, then attempts to insert that with the user id into the table. It should fail if the random string already exists (there is a unique constraint on the column). If it fails, it should generate another id and attempt to insert that (once I get working code I will add a counter to prevent hammering the db).

How would you go about writing that loop? The loop should continue if the INSERT returns an error (constraint check), otherwise loop again. I've checked the Postgres docs and can't seem to find (or am missing) the way to check the error code/status on a query.

UPDATE

I've come up with a possible solution, but need to flesh it out. The following is in pidgeon-sql, just me thinking around the problem:

success = true;
LOOP
-- create random string function
BEGIN
  insert string
EXCEPTION
  success = false;
EXIT WHEN success;
END;

Answer 1

if no need of randomness of external ID, then

CREATE SEQUENCE base_seq;
ALTER TABLE thetable
    ALTER COLUMN ext_id SET DEFAULT LPAD(nextval('base_seq')::text, 64, '0');

will give strongly unique (databasewide) strings in ext_id column

but if your only choice is to try-in-a-loop, then the loop in plpgsql function will be like this:

LOOP
  new_try_ext_id := some randomization magic here...
  INSERT INTO thetable(userid,ext_id)
    VALUES (someid, new_try_ext_id)
  ON CONFLICT DO NOTHING;
  GET DIAGNOSTICS some_integer_var = ROW_COUNT;
  EXIT WHEN some_integer_var > 0;
END LOOP;

Answer 2

Revised: Your security worry on using sequences may have some validity although I do not recall that ever coming up even in a security audit. But if it is a business requirement then you must go with it. Seems to me you need to handle key collision for multiple tables, so a generalized function generalized generating seems appropriate with a specific insert function for each table. You will need to write the insert functions for each table and cannot just use an insert statement, you must use a function (or procedure if you are using Postgres V12 or higher). You will also have to pass each column as a parameter to the insert function. The following basically 'flesh out' your pseudo-code.

create or replace function generate_random_id
                    ( lower_value_in bigint default 1  
                    , upper_value_in bigint default 10000000000)
                                                     
   returns bigint
  language sql
  volatile strict 
as $$
    select floor(random()*(upper_value_in-lower_value_in+1)+1)::bigint ;
$$; 
 
create or replace function insert_atable(col_x_in atable.colx%type)
   returns void 
  language plpgsql 
as $$
declare
    l_invalid_id boolean := true;
begin 
    while l_invalid_id
    loop
       begin
           insert into atable( id, colx)
             values ( generate_random_id(),col_x_in); 
           l_invalid_id := false;
       exception 
          when unique_violation then null;         
       end;
    end loop;
end;
$$;   

Revised demo.

Of course you can abandon the idea or the 2 ids actually being the same.

Original: So the external facing id has to be unique, but why random. Generate the id from a sequence then, limit that sequence max value as 9999999999. Then cast the generated sequence as text and store that result. That way the the internal and external ids are both unique but have the same value (at least when external cast type as id). Even better, if you have Postgres 12 of higher, you can define the external id as a generated column on id and thereby guarantee they always the same. Table definition becomes something like:

create table atable
             ( id integer  generated always as identity (maxvalue 999999999)
             , ext_id text generated always as  (id::text) stored
             , colx text 
             ) ;

See demo. Note: Demo defines id as "generated by default". This is used for demonstration purposes only.