Postgres Professional corporate blog
PostgreSQL
SQL
Comments 8
0
insert into t(a,b,c)
  select s.id, chr((32+random()*94)::integer), random() < 0.01
  from generate_series(1,100000) as s(id)
  order by random();


Rows are inserted into the table in a random order.

Here is an error. Rows won't be inserted in a random order which is expected.
0
No mistake here. You can verify the order by pageinspect extension or simply by selecting some rows from the table (in this case make sure the index is not used).

For example, I get the following (before creating the index):
   a   | b | c 
-------+---+---
 72673 |   | t
 70350 |   | t
 90569 |   | t
 42543 |   | t
 68301 |   | t
 67721 |   | t
 60639 |   | t
 82942 |   | t
 53841 |   | t
 97342 |   | t
(10 rows)


Stricktly speaking, the rows are inserted sequentially; it's SELECT that returns them in random order.
0

Actually we have random order only for a column. On b and c we have a preordered values with the same random() value on each column.


That is because an expression in the order by clause is calculated one time for row.

0
No, no, no. Your way doesn't mix the rows.

select s.id, (random()*9)::integer, (random()*9)::integer
from generate_series(1,5) as s(id)

 a | b | c 
---+---+---
 1 | 6 | 8
 2 | 4 | 0
 3 | 2 | 5
 4 | 4 | 3
 5 | 7 | 9
(5 rows)


And now you shuffle the rows:
select s.id, (random()*9)::integer, (random()*9)::integer
from generate_series(1,5) as s(id)
order by random();

 a | b | c 
---+---+---
 2 | 1 | 1
 5 | 3 | 3
 1 | 7 | 7
 4 | 7 | 7
 5 | 9 | 9
(5 rows)


Oops.
0
Ah, I see your point now. You're right. I should've used something like
WITH t AS (SELECT ...)
SELECT * FROM t ORDER BY random();

for that. Thanks!
Only those users with full accounts are able to leave comments. , please.