Rownum in postgresql
PostgresqlRow NumberRownumPostgresql Problem Overview
Is there any way to simulate rownum in postgresql ?
Postgresql Solutions
Solution 1 - Postgresql
Postgresql > 8.4
SELECT
row_number() OVER (ORDER BY col1) AS i,
e.col1,
e.col2,
...
FROM ...
Solution 2 - Postgresql
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;
Solution 3 - Postgresql
Postgresql have limit.
Oracle's code:
select *
from
tbl
where rownum <= 1000;
same in Postgresql's code:
select *
from
tbl
limit 1000
Solution 4 - 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.
Solution 5 - Postgresql
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 |
Solution 6 - Postgresql
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.
Solution 7 - Postgresql
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)
Solution 8 - Postgresql
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