On Thu, Apr 21, 2011 at 9:19 PM, David Fetter <da...@fetter.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
