Skip to content
Advertisement

How to create a sequence in a specific format in postgresql?

I want to automatically generate an unique entity field in Postgresql in following format:

“ССDDD” where “C” is a character and “D” is a digit.

For example “FG1752” or “HK9273”. How can this be implemented?

Advertisement

Answer

here is one way in mysql :

SELECT concat(char(floor(rand() *(90-65+1)+65)), 
       char(floor(rand() *(90-65+1)+65)), 
       lpad(floor(rand() *(10000)),4,0) 
) RandomAlphanumeric

in postgresql:

SELECT concat(chr(floor(random() *(90-65+1)+65)::int), 
       chr(floor(random() *(90-65+1)+65)::int), 
       lpad(floor(random() *(10000))::varchar(4),4,'0') 
) RandomAlphanumeric

I’m producing a random number between 65 and 90 which are ascii code for A to Z

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement