Re: Getting a random row

2009/10/13 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>
>
> 2009/10/13 Shaul Dar <shauldar(at)gmail(dot)com>
>>
>> Sorry, I guess I wasn't clear.
>> I have an existing table in my DB, and it doesn't have a column with
>> serial values (actually it did originally, but due to later deletions of
>> about 2/3 of the rows the column now has "holes"). I realize I could add a
>> new serial column, but prefer not to change table + the new column would
>> also become nonconsecutive after further deletions. The nice thing about
>> Oracle's "rownum" is that it' a pseudo-column", not a real one, and AFAIK is
>> always valid.
>
> change the default of that column to use sequence.
> For instance, lookup CREATE SEQUENCE in manual, and ALTER TABLE .. SET
> DEFAULT ..
>
> for example of how it looks, just create table foo(a serial), and check its
> definition with \d+ foo

This is not really what he's trying to do. Oracle's rownum has
completely different semantics than this.

But, on 8.4, a window function should do it.

...Robert