PG 17 new random functions - Postgres OnLine Journal

Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it's the small changes that bring the most joy.

Random over the years

The random() function has largely stayed stagnant for a long time. In the before times, if you needed a random number, all you had to work with was a single function random() that took no arguments and returned a double precision number that is >= 0 and < 1. When have you ever needed a random double precision number that is >= 0 and < 1? Probably never, but it did provide good ammunition to get to your real task. So how do you create a random integer between 1 and 10 in pre-PG17?

One way to do it in pre PG17 era is.

SELECT (1 + random()*10)::integer

In PostgreSQL 16 we were blessed with yet another random family function, the random_normal that you need a degree in statistics to fully appreciate written by a guy with a degree in statistics standing by with Rolling dice with postgres random function to explain this marvelous function complete with diagrams and pictures because you really need pictures and diagrams to understand this.

Random in PG 17

Finally in PG 17, we were blessed with a very intuitive and short way to answer the question that 80% of the time is what we wanted a random function for in the first place. Return an integer between 1 and 10:

SELECT random(1,10);

Not only is this code shorter, it is also clearer in intent.

Now if that were not sweet enough, suppose you wanted to create random money amounts. Money at least in US has only 2 decimals. How would you create a random amount between 1 and 10 dollars

In Pre PG 17 era, I'd do this

SELECT (1 + random()*10)::numeric(4,2);

In PG 17, I can do this:

SELECT random(1.00,10.00);

Which would return a number such as 5.79, that will always have exactly 2 decimals. If your two numbers had different number of decimals, it would return the greatest number of decimals of either number. So:

SELECT random(1.00, 10.000);
would return a number like 9.657