0

I have this statement which generate a random string:

SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM generate_series(1, 20);

My question, how to implement this inside a function which would assign the value of the above statement automatically to a field named url_prefix when new records are created (on INSERT's) ?

3

You can create a function and use it as the default for the column:

create function f() returns text language sql as $$
  SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
  FROM generate_series(1, 20)
$$;

create table foo(id serial primary key, url_prefix text not null default f());

insert into foo default values;
select * from foo;
id | url_prefix          
-: | :-------------------
 1 | iByDaJ2h4JHi3dI3uBoy

db<>fiddle here

  • 1
    how does this guarantee uniqueness? – Jonathan Fite Aug 28 at 16:58
  • Thanks this indeed generates the random string upon record creation. Still, I wonder if it is possible to expand the code further to handle the very odd situation that the generated string already exists in the database. A code that regenerates the string until it is indeed unique for the field in the table. Can this be handled by PostgreSQL or is there a need to implement that check in the application code? – W.M. Aug 28 at 19:59
  • 1
    @W.M. I'd put a unique constraint on the url_prefix column — that guarantees uniqueness. Then I'd either make sure my random string is long enough that the chances of a clash are vanishingly small, or use a UUID. Without knowing more about your actual requirements, I couldn't say which is right for you though. – Jack Douglas Aug 28 at 21:04
  • 1
    @JonathanFite Good point, I read the question but not the title, will try to do both next time! – Jack Douglas Aug 28 at 21:05
  • I would definitely put the unique index on the field. But also have the application check specifically for unique constraint violations and have some retry logic put in place to hide that error from the user (let other errors bubble out, but catch and retry that specific one). It may be vanishingly small, but it will happen so might as well account for the edge case since it's known, testable and safe to retry by the application layer. – Jonathan Fite Aug 29 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.