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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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