Thread: oracle rownum equivalent?

is there an equivalent to rownum in oracle?

also, where can one find reference to "hidden columns"
such as OID or, as in oracle, rownum?

thanks,

  mikeo

mikeo wrote:
>
> is there an equivalent to rownum in oracle?
>
> also, where can one find reference to "hidden columns"
> such as OID or, as in oracle, rownum?

oid is the equivalent.  not sure documentation exists for these...

Regards,
Ed Loehr

thanks for the response.  oid is equivalent to oracle rowid.
rownum can be used similar to the limit option of select in postgres
but in oracle it can also be used in a where clause, or as an assigment
in an update statement for instance.

eg: update ctmaster set bsc_id = mod(rownum,3) +1;

this gives me a way to assign streams to rows in a load balanced manner
on the fly, for example.  i use it in other more involved ways than this
also.  i cannot do this with limit.  i could do this with sequence with
a max value but i'd have to define a sequence each time i wanted to do
something "on the fly" or for what ifs.

what i'm also interested in is how to find reference to these type of
pseudo-columns, even just the names of them, if they're listed somewhere.

thanks again,
       mikeo

At 08:29 AM 6/7/00 -0500, Ed Loehr wrote:
>mikeo wrote:
>>
>> is there an equivalent to rownum in oracle?
>>
>> also, where can one find reference to "hidden columns"
>> such as OID or, as in oracle, rownum?
>
>oid is the equivalent.  not sure documentation exists for these...
>
>Regards,
>Ed Loehr
>

mikeo wrote:
>
> thanks for the response.  oid is equivalent to oracle rowid.
> rownum can be used similar to the limit option of select in postgres
> but in oracle it can also be used in a where clause, or as an assigment
> in an update statement for instance.
>
> eg: update ctmaster set bsc_id = mod(rownum,3) +1;
>
> this gives me a way to assign streams to rows in a load balanced manner
> on the fly, for example.  i use it in other more involved ways than this
> also.  i cannot do this with limit.  i could do this with sequence with
> a max value but i'd have to define a sequence each time i wanted to do
> something "on the fly" or for what ifs.
>
> what i'm also interested in is how to find reference to these type of
> pseudo-columns, even just the names of them, if they're listed somewhere.

I think this might be the list, but you might query pgsql-hackers for
more info.  There was a recent thread involving this..

    ctid
    oid
    xmin (minimum transaction number)
    xmax
    cmin (minimum command number)
    cmax

ctid may be what you're looking for, but I don't understand very well how
these are used.  Maybe someone else can say or you can experiment...

Regards,
Ed Loehr

(PS:  Posting to only one of -general or -sql will almost always be
sufficient.)

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Ed Loehr
>
> mikeo wrote:
> >
> > thanks for the response.  oid is equivalent to oracle rowid.

IMHO,tid is equivalent to Oracle's rowid.

> > rownum can be used similar to the limit option of select in postgres
> > but in oracle it can also be used in a where clause, or as an assigment
> > in an update statement for instance.
> >
> > eg: update ctmaster set bsc_id = mod(rownum,3) +1;
> >
> > this gives me a way to assign streams to rows in a load balanced manner
> > on the fly, for example.  i use it in other more involved ways than this
> > also.  i cannot do this with limit.  i could do this with sequence with
> > a max value but i'd have to define a sequence each time i wanted to do
> > something "on the fly" or for what ifs.
> >
> > what i'm also interested in is how to find reference to these type of
> > pseudo-columns, even just the names of them, if they're listed
> somewhere.
>
> I think this might be the list, but you might query pgsql-hackers for
> more info.  There was a recent thread involving this..
>
>     ctid
>     oid
>     xmin (minimum transaction number)
>     xmax
>     cmin (minimum command number)
>     cmax
>
> ctid may be what you're looking for, but I don't understand very well how
> these are used.  Maybe someone else can say or you can experiment...
>

AFAIK,there's no pseudo-column like Oracle's rownum which is
dynamically allocated at execution time.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp