PostgreSQL SQL - Random()

Author

Random()

Ezequias Rodrigues da Rocha

2006-11-16, 7:16 pm

Hi list,

I have a bigint collumn and I would like to generate a random number within
the numbers of my column.

Is it possible ?

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

A. Kretschmer

2006-11-16, 7:16 pm

am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da Rocha folgendes:
> Hi list,
>
> I have a bigint collumn and I would like to generate a random number within the
> numbers of my column.

select <bigint_column> from <your_table> order by random() limit 1;

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Ezequias Rodrigues da Rocha

2006-11-16, 7:16 pm

Thank you so much andreas.

Regards
http://ezequiasrocha.blogspot.com

2006/11/16, A. Kretschmer <andreas. kretschmer@schollgla
s.com>:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> within the
>
> select <bigint_column> from <your_table> order by random() limit 1;
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Ezequias Rodrigues da Rocha

2006-11-16, 7:16 pm

Just another question. I can trust on this random number ? The rule to it is
quite tested ?

I am thinking in implementing a function that return allways a random
number. What do you think of the trust of this function (random()) ?

My bigint field has 20,000 numbers and I'd like to make real random (used
like lottery).

Regards
Ezequias

2006/11/16, A. Kretschmer <andreas. kretschmer@schollgla
s.com>:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> within the
>
> select <bigint_column> from <your_table> order by random() limit 1;
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Andrew Sullivan

2006-11-16, 7:16 pm

On Thu, Nov 16, 2006 at 06:54:42PM -0200, Ezequias Rodrigues da Rocha wrote:
> My bigint field has 20,000 numbers and I'd like to make real random (used
> like lottery).

If you want _real_ random, you have to use a non-computer source for
your randomness. The best you get on a computer is pseudo-random.
Often it's very good pseudo-random -- most of the time, good enough
for most purposes -- but it isn't true randomness of the sort that is
delivered by (for example) particle decay.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Aaron Bono

2006-11-17, 7:14 pm

On 11/16/06, A. Kretschmer <andreas. kretschmer@schollgla
s.com> wrote:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> within the
>
> select <bigint_column> from <your_table> order by random() limit 1;

Is this a PostgreSQL thing or is it SQL standard?

--
====================
====================
====================
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
====================
====================
====================
======

Rajesh Kumar Mallah

2006-11-18, 7:16 pm

On 11/17/06, A. Kretschmer <andreas. kretschmer@schollgla
s.com> wrote:
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> within the
>

> select <bigint_column> from <your_table> order by random() limit 1;

This query will tend to get slower as the table grows because of the sorting.

it possible to get a row from a random offset
how about

select <bigint_column> from <your_table> limit 1 offset 20000*random() ;

?

regds
mallah.

>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

A. Kretschmer

2006-11-18, 7:16 pm

am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes:
>
> This query will tend to get slower as the table grows because of the
> sorting.

Right.

>
> it possible to get a row from a random offset
> how about
>
> select <bigint_column> from <your_table> limit 1 offset 20000*random() ;

For instance, you have a table with only 500 rows and random() returns
0.999? In other words: in think, this is a bad idea, unless you use a
constant for the offset-calculation thats not larger than the number of
rows in this table. For this, you need this value, but select count(1)
from table force a seqscan -> slow.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Rajesh Kumar Mallah

2006-11-18, 7:16 pm

On 11/18/06, A. Kretschmer <andreas. kretschmer@schollgla
s.com> wrote:
>
> am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah
> folgendes:
>
> Right.
>
>
> ;
>
> For instance, you have a table with only 500 rows and random() returns
> 0.999? In other words: in think, this is a bad idea, unless you use a
> constant for the offset-calculation thats not larger than the number of
> rows in this table. For this, you need this value, but select count(1)
> from table force a seqscan -> slow.

but order by random() would also forces a seqscan , looks like the random
offset method performs
better

explain analyze select count(*) from general.profile_master;
+--------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------+
| Aggregate (cost=139214.26..139214.27 rows=1 width=0) (actual time=
3071.178..3071.179 rows=1 loops=1) |
| -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421
width=0) (actual time=0.032..2686.842 rows=601240 loops=1) |
| Total runtime: 3071.268ms
|
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)

tradein_clients=> explain analyze SELECT profile_id from
general.profile_master limit 1 offset 601240*random();
+--------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------+
| Limit (cost=13770.30..13770.53 rows=1 width=4) (actual time=
1614.146..1614.147 rows=1 loops=1) |
| -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421
width=4) (actual time=0.036..1375.742 rows=429779 loops=1) |
| Total runtime: 1614.187ms
|
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)

tradein_clients=> explain analyze SELECT profile_id from
general.profile_master order by random() limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| Limit (cost=207079.39..207079.40 rows=1 width=4) (actual time=
11715.694..11715.695 rows=1 loops=1) |
| -> Sort (cost=207079.39..208590.45 rows=604421 width=4) (actual time=
11715.691..11715.691 rows=1 loops=1) |
| Sort Key:
random()
|
| -> Seq Scan on profile_master (cost=0.00..139214.26 rows=604421
width=4) (actual time=0.036..4605.259 rows=601241 loops=1) |
| Total runtime: 11716.039ms
|
+--------------------------------------------------------------------------------------------------------------------------------------+
(5 rows)

Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Sponsored Links