Question 411908 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Generate a Series of Numbers

I am trying to generate random serial numbers in PostgreSQL with some conditions.

  1. The numbers need to start with the same 3 letters, YAK.
  2. After YAK, there needs to be 8 random digits.
  3. I only need to do 10 iterations of this.

I know I should try to start with generate_series() but can I use RANDOM() in there at all like I probably would for python?

Tags:

2

Answers


  1. 0

    You seem to have all the building blocks, you just need to put them together:

    • generate_series can be used in the from clause to generate 10 rows
    • For each of these rows, you can use random and multiply it by 100,000,000 to generate a random number between 0 and 99,999,999.
    • Then, you just need to pad that number up to eight digits and slap a ‘YAK’ before it:
    SELECT CONCAT('YAK', LPAD(FLOOR(RANDOM() * 100000000)::TEXT, 8, '0'))
FROM   GENERATE_SERIES(1, 10)

    SQLFiddle demo

    Login or Signup to reply.
  2. 0

    You can implement a function to randomize, like

    CREATE FUNCTION myrandom(varchar, integer, integer) RETURNS varchar
    AS 'select concat($1, LPAD(FLOOR(RANDOM() * $2)::TEXT, $3, ''0''))'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
    
select(myrandom('YAK', 100000000, 10));

    Parameters:

    • prefix
    • pool size
    • character length

    Explanation: You select a randomized number based on pool size, LPAD resolves the character length and you concatenate the result to the prefix.

    Fiddle: https://sqlfiddle.com/postgresql/online-compiler?id=20edf888-8258-418c-9ba3-31c22dcc30e7

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search