PostgreSQL中的Rownum

Question

有没有办法在PostgreSQL中模拟rownum？

- johnlemon

Answer 1

PostgreSQL > 8.4

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

- baklarz2048

---

请注意，这与 Oracle 的 rownum 类似，但略有不同。Oracle 的 rownum 是在从磁盘读取行时分配的，而这个 row_number() 则取决于您的 OVER 子句中包含的内容。 - Royce

速度差异怎么样？ - Nashev

Answer 2

Postgresql 有限制。

Oracle 的代码：

select *
from
  tbl
where rownum <= 1000;

Postgresql的代码中也一样：

select *
from
  tbl
limit 1000

- Buka

---

我更喜欢使用ANSI SQL:2008语法：FETCH N ROWS ONLY（适用于Oracle 12、Postgres等...） 请查看文档：https://www.postgresql.org/docs/current/sql-select.html - R. Du

@R.Du，我最喜欢您的评论，我认为它应该是一个答案。 - learning2learn

Answer 3

我刚在Postgres 9.1中测试了一种与Oracle ROWNUM接近的解决方案：

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

- Wolfgang Vollmann

---

注意，这将不会考虑外部查询中的 ORDER BY。 - Gordon Linoff

Answer 4

如果你只需要返回一个数字，请尝试以下方法。

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

您可以在inline_v1 SQL中添加order by，使得ROWNUM对数据有一定的顺序意义。

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

这可能不是最快的方法，但如果你确实需要它们，这是一个选项。

- Kuberchaun

Answer 5

如果您有一个唯一的关键字，可以使用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

- Kaushik Nayak

---

这正是我需要的，它解决了我的问题。谢谢！ - dbusern

Answer 6

我认为可以使用临时序列来模拟Oracle的rownum。

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;

演示：

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

提供：

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

解释：

函数rownum_seq()是不可变的，在查询中仅被PG调用一次，因此我们得到相同的唯一序列名称（即使该函数在同一查询中调用了一千次）。

函数rownum()是易变的，并且每次都被PG调用（甚至在where子句中也会被调用）。

没有r记录参数（未使用），函数rownum()可能会过早地被评估。这是棘手的问题。想象一下下面的rownum()函数:

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在排序之前应用过滤器。该死！ 使用第一个未使用的参数，我们强制PG在过滤器之前排序：

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)

优点：

• 可用于表达式或where子句中
• 易于使用：只需传递from子句中的第一条记录*

缺点：

• 每次遇到rownum()都会创建一个临时序列，但在会话结束时会被删除。
• 性能（需要讨论row_number() over ()与nextval之间的区别）

- Krapo

Answer 7

Postgresql没有Oracle的ROWNUM等价物。 在许多情况下，您可以通过在查询中使用LIMIT和OFFSET来实现相同的结果。

- Sachin Shanbhag

Answer 8

使用limit关键字，并结合offset来选择行号，如果你想获取第8行，则使用： limit 1 offset 7

- Lorenzo Zuluaga