Re: How to realize ROW_NUMBER() in 8.3?

On Thu, Apr 21, 2011 at 9:19 PM, David Fetter <david(at)fetter(dot)org> wrote:

> On Wed, Apr 20, 2011 at 11:51:25AM -0400, Emi Lu wrote:
> > Hello,
> >
> > ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to
> > get row_number
> >
> > select row_number(), col1, col2...
> > FROM tableName
> >
> > Thanks a lot!
> > 丁叶
>
> Your best bet is to upgrade to a modern version of PostgreSQL. While
> you will of course need to do tests with your applications, 9.0 has no
> significant backward-incompatibility with 8.3.
>

Hmm, PostgreSQL 8.3 does not support the ROWNUM feature, however, a possible
work around can be achieved by using the LIMIT and OFFSET options.

psql=# SELECT empno FROM emp LIMIT 10

The above query will display the first 10 records.

You can also use the (auto incrementing) SERIAL data type as a ROWNUM column
to simulate the ROWNUM feature.

Something like this...

psql=# create table rownumtest(rownum SERIAL, val1 varchar, val2 int4);

psql=# insert into rownumtest(val1,val2) values('abc', '1');

psql=# insert into rownumtest(val1,val2) values('def', '2');

psql=# insert into rownumtest(val1,val2) values('ghi', '3');

psql=# insert into rownumtest(val1,val2) values('jkl', '4');

psql=# select * from rownumtest;

rownum | val1 | val2

--------+------+------

1 | abc | 1

2 | def | 2

3 | ghi | 3

4 | jkl | 4

Hope this helps....

--Raghu Ram