Rownum in postgresql

Question

Is there any way to simulate rownum in postgresql ?

Songsongbird

Answer 1

Postgresql > 8.4

SELECT 
    row_number() OVER (ORDER BY col1) AS i, 
    e.col1, 
    e.col2, 
    ... 
FROM ... 

Aleurone

Be careful. This is similar to oracle's rownum, but it's slightly different. Oracle's rownum is assigned at time of reading the row from disk, whereas this row_number() is assigned depending upon what's in your OVER – Konikow

What about speed differences? – Cuthbert

Answer 2

Postgresql have limit.

Oracle's code:

select *
from
  tbl
where rownum <= 1000;

same in Postgresql's code:

select *
from
  tbl
limit 1000

Isadora

I would prefer the ANSI SQL:2008 syntax : FETCH N ROWS ONLY (works on Oracle 12, Postgres, ...) check the doc : postgresql.org/docs/current/sql-select.html – Suber

@R.Du I like your comment the best and I think it should be an answer. – Sumac

Answer 3

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;

Hourglass

Note that this will not respect an ORDER BY in the outer query. – Expect

Answer 4

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.

Flotation

Answer 5

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

Beefburger

This was the exact need I had and it solved my issue. Thanks!! – Quadrivial

Answer 6

I think it's possible to mimic Oracle rownum using temporary sequences.

create or replace function rownum_seq() returns text as $$
select concat('seq_rownum_',replace(uuid_generate_v4()::text,'-','_'));
$$ language sql immutable;

create or replace function rownum(r record, v_seq_name text default rownum_seq()) returns bigint as $$
declare 
begin
    return nextval(v_seq_name);
exception when undefined_table then
    execute concat('create temporary sequence ',v_seq_name,' minvalue 1 increment by 1');
    return nextval(v_seq_name);
end;
$$ language plpgsql volatile;

Demo:

select ccy_code,rownum(a.*) from (select ccy_code from currency order by ccy_code desc) a where rownum(a.*)<10;

Gives:

ZWD 1
ZMK 2
ZBH 3
ZAR 4
YUN 5
YER 6
XXX 7
XPT 8
XPF 9

Explanations:

Function rownum_seq() is immutable, called only once by PG in a query, so we get the same unique sequence name (even if the function is called thousand times in the same query)

Function rownum() is volatile and called each time by PG (even in a where clause)

Without r record parameter (which is unused), the function rownum() could be evaluated too early. That's the tricky point. Imagine, the following rownum() function:

create or replace function rownum(v_seq_name text default rownum_seq()) returns bigint as $$
declare 
begin
    return nextval(v_seq_name);
exception when undefined_table then
    execute concat('create temporary sequence ',v_seq_name,' minvalue 1 increment by 1');
    return nextval(v_seq_name);
end;
$$ language plpgsql volatile;


explain select ccy_code,rownum() from (select ccy_code from currency order by ccy_code desc) a where rownum()<10

Sort  (cost=56.41..56.57 rows=65 width=4)
  Sort Key: currency.ccy_code DESC
  ->  Seq Scan on currency  (cost=0.00..54.45 rows=65 width=4)
        Filter: (rownum('649aec1a-d512-4af0-87d8-23e8d8a9d982'::text) < 10)

PG apply the filter before the order. Damned! With the first unused parameter, we force PG to order before filter:

explain select * from (select ccy_code from currency order by ccy_code desc) a where rownum(a.*)<10;

Subquery Scan on a  (cost=12.42..64.36 rows=65 width=4)
  Filter: (rownum(a.*, 'seq_rownum_43b5c67f_dd64_4191_b29c_372061c848d6'::text) < 10)
  ->  Sort  (cost=12.42..12.91 rows=196 width=4)
        Sort Key: currency.ccy_code DESC
        ->  Seq Scan on currency  (cost=0.00..4.96 rows=196 width=4)

Pros:

• works as an expression or in a where clause
• easy to use: just pass the first record.* you have in the from

Cons:

• a temporary sequence is created for each rownum() encountered, but it is removed when session ends.
• performance (to discuss, row_number() over () versus nextval)

Holston

Answer 7

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.

Yockey

Answer 8

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

Cheree