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