pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedabout select use random
4+ messages / 2 participants
[nested] [flat]
* about select use random
@ 2012-07-25 02:22 wangqi <wangqi@edgesoft.cn>
0 siblings, 1 reply; 4+ messages in thread
From: wangqi @ 2012-07-25 02:22 UTC (permalink / raw)
To: pgsql-admin
Hi everyone
■SQL-1
CREATE TABLE wk_mclid1(
id1 integer ,
PRIMARY KEY(id1)
);
■SQL-2
INSERT INTO wk_mclid1(id1)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
GO
■SQL-3
select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
+ 1)::integer;
When we excute SQL-3,sometimes the result was 0 record or more than 1
record.
Why?
============================================================================================
select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
+ 1)::integer;
id1
------
9
============================================================================================
select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
+ 1)::integer;
id1
------
4
6
7
============================================================================================
select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
+ 1)::integer;
id1
------
0 record(s) selected [Fetch MetaData: 94/ms] [Fetch Data: 0/ms]
============================================================================================
thanks!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: about select use random
@ 2012-07-25 02:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: wangqi <wangqi@edgesoft.cn>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2012-07-25 02:55 UTC (permalink / raw)
To: wangqi <wangqi@edgesoft.cn>; +Cc: pgsql-admin
wangqi <wangqi@edgesoft.cn> writes:
> ¡öSQL-3
> select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
> + 1)::integer;
> When we excute SQL-3,sometimes the result was 0 record or more than 1
> record.
> Why?
random() is re-evaluated for each row scanned by the SELECT.
If you don't want that, you can use a WITH to lock down the result of a
single random() call.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: about select use random
@ 2012-07-25 03:33 wangqi <wangqi@edgesoft.cn>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: wangqi @ 2012-07-25 03:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks Tom, I try it.
于 2012-7-25 10:55, Tom Lane 写道:
> wangqi <wangqi@edgesoft.cn> writes:
>> ■SQL-3
>> select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
>> + 1)::integer;
>> When we excute SQL-3,sometimes the result was 0 record or more than 1
>> record.
>> Why?
> random() is re-evaluated for each row scanned by the SELECT.
>
> If you don't want that, you can use a WITH to lock down the result of a
> single random() call.
>
> regards, tom lane
>
--
----------------------------------------------------
Yours sincerely,
Qi Wang
Edgesoft (China) Co.,Ltd
F26th Block B,Zhongli Building,
No.32 Huoju Road,
Hi-Tech Zone,Dalian,China
Zip:116023
Tel:+86-411-84753511
Fax:+86-411-84753577
http://www.edgesoft.cn
mailto:wangqi@edgesoft.cn
----------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: about select use random
@ 2012-07-25 05:18 wangqi <wangqi@edgesoft.cn>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: wangqi @ 2012-07-25 05:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom
If you don't want that, you can use a WITH to lock down the result of a
single random() call.
How should I do it?
于 2012-7-25 10:55, Tom Lane 写道:
> wangqi <wangqi@edgesoft.cn> writes:
>> ■SQL-3
>> select * from wk_mclid1 where id1= (round((random() * (10-1))::integer)
>> + 1)::integer;
>> When we excute SQL-3,sometimes the result was 0 record or more than 1
>> record.
>> Why?
> random() is re-evaluated for each row scanned by the SELECT.
>
> If you don't want that, you can use a WITH to lock down the result of a
> single random() call.
>
> regards, tom lane
>
--
----------------------------------------------------
Yours sincerely,
Qi Wang
Edgesoft (China) Co.,Ltd
F26th Block B,Zhongli Building,
No.32 Huoju Road,
Hi-Tech Zone,Dalian,China
Zip:116023
Tel:+86-411-84753511
Fax:+86-411-84753577
http://www.edgesoft.cn
mailto:wangqi@edgesoft.cn
----------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-07-25 05:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-25 02:22 about select use random wangqi <wangqi@edgesoft.cn>
2012-07-25 02:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-07-25 03:33 ` wangqi <wangqi@edgesoft.cn>
2012-07-25 05:18 ` wangqi <wangqi@edgesoft.cn>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox