Since my approach for a test query which I worked on in this question did not work out, I'm trying something else now. Is there a way to tell pg's random()
function to get me only numbers between 1 and 10?
10 Answers
If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, then it's easy:
select random() * 9 + 1
This can be easily tested with:
# select min(i), max(i) from (
select random() * 9 + 1 as i from generate_series(1,1000000)
) q;
min | max
-----------------+------------------
1.0000083274208 | 9.99999571684748
(1 row)
If you want integers, that are >= 1 and < 10, then it's simple:
select trunc(random() * 9 + 1)
And again, simple test:
# select min(i), max(i) from (
select trunc(random() * 9 + 1) as i from generate_series(1,1000000)
) q;
min | max
-----+-----
1 | 9
(1 row)

- 12,280
- 5
- 59
- 52
-
1select date(e.created_at) + (trunc(random() * 20)) from events e; result in: ERROR: operator does not exist: date + double precision Does trunc really return integers? – Bogdan Gusiev Jan 26 '10 at 12:44
-
5`trunc()` returns the same data type as the input (as stated in the manual). You need to cast the result to an integer: `trunc(random() * 20)::int` – Oct 05 '11 at 17:35
-
I wonder if at least in theory it is possible that `random()` would return a value <1 that when multiplied by 9 would be >=9 due to the [inexact nature of the double precision type](http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-FLOAT)? In practice even if it is possible it would be vanishingly unlikely of course because of the 15 digits or so precision. – Aug 14 '12 at 14:54
-
1I'm toying with `width_bucket(random(), 0, 1, 10)` as an alternative – Aug 14 '12 at 15:29
-
It looks like [my fears were groundless](http://cs.stackexchange.com/a/3186/2515) though I confess I don't understand the maths at all :-) – Aug 15 '12 at 07:46
-
this is not correct for SQL, In SQL 2014 i got error 'random' is not a recognized built-in function name. – Avinash Jain Jun 20 '16 at 07:54
To summarize and a bit simplify, you can use:
-- 0 - 9
select floor(random() * 10);
-- 0 - 10
SELECT floor(random() * (10 + 1));
-- 1 - 10
SELECT ceil(random() * 10);
And you can test this like mentioned by @user80168
-- 0 - 9
SELECT min(i), max(i) FROM (SELECT floor(random() * 10) AS i FROM generate_series(0, 100000)) q;
-- 0 - 10
SELECT min(i), max(i) FROM (SELECT floor(random() * (10 + 1)) AS i FROM generate_series(0, 100000)) q;
-- 1 - 10
SELECT min(i), max(i) FROM (SELECT ceil(random() * 10) AS i FROM generate_series(0, 100000)) q;

- 349
- 2
- 5
-
2The [docs say](https://www.postgresql.org/docs/10/static/functions-math.html) "random value in the range 0.0 <= x < 1.0", so there is at least a theoretical chance of `ceil(random() * 10)` resulting in 0 — I would stick to `floor`. – Jan 15 '18 at 08:48
-
1I agree with @JackDouglas, so for the range 1 - 10 it should be `SELECT floor(random() * 10 + 1);` – SergiyKolesnikov Aug 23 '19 at 11:01
If you are using SQL Server then correct way to get integer is
SELECT Cast(RAND()*(b-a)+a as int);
Where
- 'b' is the upper limit
- 'a' is lower limit

- 7,200
- 2
- 26
- 40
-
Be careful here, if you put your lower limit as 1 and upper as 10, you will only get numbers 1->9. Other answers seem to assume that Between 1 and 10 means 1->9... I would suggest if 'between' excludes the upper bound it should also exclude the lower (ie 2->9). SELECT Cast(RAND()*((b+1)-a)+a as int); – Morvael Dec 08 '16 at 09:09
-
1
(trunc(random() * 10) % 10) + 1

- 2,377
- 15
- 23
-
-
1And why would you use modulus anyway? This logic doesn't make sense. If you get any "wrapping" you won't have equal distribution, and if you don't get any, then you don't need it. – ErikE Aug 14 '12 at 22:47
Using Postgres, here is how to generate random number between any 2 numbers, say, min
and max
:
Including min
and Excluding max
,
SELECT floor(random() * (max - min)) + min;
Including both min
and max
,
SELECT floor(random() * (max - min + 1)) + min;
So to get numbers between 1 and 10 (including 10),
min = 1, max = 10
SELECT floor(random() * (10 - 1 + 1)) + 1;
In general, you can use this formula to get random integer numbers between any min and max numbers.

- 3,143
- 1
- 32
- 42
The correct version of hythlodayr's answer.
-- ERROR: operator does not exist: double precision % integer
-- LINE 1: select (trunc(random() * 10) % 10) + 1
The output from trunc
has to be converted to INTEGER
. But it can be done without trunc
. So it turns out to be simple.
select (random() * 9)::INTEGER + 1
Generates an INTEGER output in range [1, 10] i.e. both 1 & 10 inclusive.
For any number (floats), see user80168's answer. i.e just don't convert it to INTEGER
.

- 3,143
- 1
- 32
- 42
Actually I don't know you want to this.
try this
INSERT INTO my_table (my_column)
SELECT
(random() * 10) + 1
;

- 709
- 7
- 7
This stored procedure inserts a rand number into a table. Look out, it inserts an endless numbers. Stop executing it when u get enough numbers.
create a table for the cursor:
CREATE TABLE [dbo].[SearchIndex](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Cursor] [nvarchar](255) NULL)
GO
Create a table to contain your numbers:
CREATE TABLE [dbo].[ID](
[IDN] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL)
INSERTING THE SCRIPT :
INSERT INTO [SearchIndex]([Cursor]) SELECT N'INSERT INTO ID SELECT FLOOR(rand() * 9 + 1) SELECT COUNT (ID) FROM ID
CREATING AND EXECUTING THE PROCEDURE:
CREATE PROCEDURE [dbo].[RandNumbers] AS
BEGIN
Declare CURSE CURSOR FOR (SELECT [Cursor] FROM [dbo].[SearchIndex] WHERE [Cursor] IS NOT NULL)
DECLARE @RandNoSscript NVARCHAR (250)
OPEN CURSE
FETCH NEXT FROM CURSE
INTO @RandNoSscript
WHILE @@FETCH_STATUS IS NOT NULL
BEGIN
Print @RandNoSscript
EXEC SP_EXECUTESQL @RandNoSscript;
END
END
GO
Fill your table:
EXEC RandNumbers

- 22,834
- 10
- 68
- 88
Try This:
Select (ROW_NUMBER() OVER (ORDER BY ItemDesc ASC)+15000) as ID, ItemCode, ItemDesc

- 2,890
- 20
- 28
- 35