[postgresql] Rownum in postgresql

Is there any way to simulate rownum in postgresql ?

If you just want a number to come back try this.

create temp sequence temp_seq;
SELECT inline_v1.ROWNUM,inline_v1.c1
FROM
(
select nextval('temp_seq') as ROWNUM, c1 
from sometable
)inline_v1;

You can add a order by to the inline_v1 SQL so your ROWNUM has some sequential meaning to your data.

select nextval('temp_seq') as ROWNUM, c1 
from sometable
ORDER BY c1 desc;

Might not be the fastest, but it's an option if you really do need them.

I have just tested in Postgres 9.1 a solution which is close to Oracle ROWNUM:

select row_number() over() as id, t.*
from information_schema.tables t;

Postgresql does not have an equivalent of Oracle's ROWNUM. In many cases you can achieve the same result by using LIMIT and OFFSET in your query.

use the limit clausule, with the offset to choose the row number -1 so if u wanna get the number 8 row so use:

limit 1 offset 7

Postgresql have limit.

Oracle's code:

select *
from
  tbl
where rownum <= 1000;

same in Postgresql's code:

select *
from
  tbl
limit 1000

If you have a unique key, you may use COUNT(*) OVER ( ORDER BY unique_key ) as ROWNUM

SELECT t.*, count(*) OVER (ORDER BY k ) ROWNUM 
FROM yourtable t;

| k |     n | rownum |
|---|-------|--------|
| a | TEST1 |      1 |
| b | TEST2 |      2 |
| c | TEST2 |      3 |
| d | TEST4 |      4 |

DEMO