Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 157AF1EE4BDB
	for <pgsql-admin@postgresql.org>; Tue, 24 Jul 2012 23:55:49 -0300 (ADT)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1StrlL-0005ma-J8
	for pgsql-admin@postgresql.org; Wed, 25 Jul 2012 02:55:48 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6P2tTld012604;
	Tue, 24 Jul 2012 22:55:29 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: wangqi <wangqi@edgesoft.cn>
cc: pgsql-admin@postgresql.org
Subject: Re: about select use random
In-reply-to: <500F5870.8010400@edgesoft.cn>
References: <500F5870.8010400@edgesoft.cn>
Comments: In-reply-to wangqi <wangqi@edgesoft.cn>
	message dated "Wed, 25 Jul 2012 10:22:40 +0800"
Date: Tue, 24 Jul 2012 22:55:29 -0400
Message-ID: <12603.1343184929@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/72
X-Sequence-Number: 39789

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