Skip site navigation (1) Skip section navigation (2)

Re: BUG #6607: Strange select behavior

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <suvisor(dot)root(at)gmail(dot)com>,<pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #6607: Strange select behavior
Date: 2012-04-23 14:53:03
Message-ID: 4F95267F020000250004723E@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
<suvisor(dot)root(at)gmail(dot)com> wrote:
 
> select * from testt where id = (random()* 100000)::integer;
> 
> And sometimes it comes out something like this:
> id | val
> -------+--------
> 11894 | 15051
> 29233 | 42198
> 80725 | 90213
> 85688 | 100992
> 88017 | 108075
> (5 rows)
> Here can be 2, 3 or other rows amount in result... But must be
> only one!
 
No, what you have written will scan the entire table and give each
row a 1 in 100000 chance of being selected.  Maybe something like
this would give you what you want:
 
select t.* from (select (random()* 100000)::integer) n(r)
  join testt t on (t.id = n.r);
 
By the way, you might want to tweak that random number before
casting it to int, or you might not get *any* rows back:
 
test=# select ('0.0000000001'::float * 100000)::int;
 int4 
------
    0
(1 row)
 
Maybe something like:
 
(select floor(random() * 100000)::int + 1)
 
-Kevin

In response to

Responses

pgsql-bugs by date

Next:From: biju.georgeDate: 2012-04-23 15:16:40
Subject: BUG #6609: pattern matching (version 8.2 or so...)
Previous:From: Tom LaneDate: 2012-04-23 13:22:42
Subject: Re: BUG #6605: wrong type cast from timestamp to timestamptz

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group