标签
PostgreSQL , rownum , Oracle 兼容性 , row_number 窗口 , limit , PPAS , EDB
背景
Oracle ROWNUM是一个虚拟列，每输出一行递增1。
Oracle rownum
通常被用于LIMIT输出记录数。
SELECT ROWNUM, empno, ename, job FROM emp WHERE ROWNUM < 5 ORDER BY ename;
rownum | empno | ename | job
--------+-------+-------+----------
2 | 7499 | ALLEN | SALESMAN
4 | 7566 | JONES | MANAGER
1 | 7369 | SMITH | CLERK
3 | 7521 | WARD | SALESMAN
(4 rows)
或者用于生成序列值。
ALTER TABLE jobhist ADD seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;
SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS start, job FROM jobhist;
seqno | empno | start | job
-------+-------+-----------+-----------
1 | 7369 | 17-DEC-80 | CLERK
2 | 7499 | 20-FEB-81 | SALESMAN
3 | 7521 | 22-FEB-81 | SALESMAN
4 | 7566 | 02-APR-81 | MANAGER
5 | 7654 | 28-SEP-81 | SALESMAN
6 | 7698 | 01-MAY-81 | MANAGER
7 | 7782 | 09-JUN-81 | MANAGER
8 | 7788 | 19-APR-87 | CLERK
9 | 7788 | 13-APR-88 | CLERK
10 | 7788 | 05-MAY-90 | ANALYST
11 | 7839 | 17-NOV-81 | PRESIDENT
12 | 7844 | 08-SEP-81 | SALESMAN
13 | 7876 | 23-MAY-87 | CLERK
14 | 7900 | 03-DEC-81 | CLERK
15 | 7900 | 15-JAN-83 | CLERK
16 | 7902 | 03-DEC-81 | ANALYST
17 | 7934 | 23-JAN-82 | CLERK
(17 rows)
PostgreSQL rownum
PostgreSQL 目前没有rownum虚拟列，但是实现同样的功能确很容易：
1、输出行号，使用临时序列
postgres=# create temp sequence if not exists tmp_seq;
postgres=# alter sequence tmp_seq restart with 1;
postgres=# select nextval('tmp_seq') as rownum, * from test limit 10;
rownum | id | info | crt_time
--------+----+------+----------------------------
1 | 1 | test | 2018-01-24 11:06:24.882708
2 | 2 | test | 2018-01-24 11:06:24.882708
3 | 3 | test | 2018-01-24 11:06:24.882708
4 | 4 | test | 2018-01-24 11:06:24.882708
5 | 5 | test | 2018-01-24 11:06:24.882708
6 | 6 | test | 2018-01-24 11:06:24.882708
7 | 7 | test | 2018-01-24 11:06:24.882708
8 | 8 | test | 2018-01-24 11:06:24.882708
9 | 9 | test | 2018-01-24 11:06:24.882708
10 | 10 | test | 2018-01-24 11:06:24.882708
(10 rows)
2、输出行号，使用窗口函数
postgres=# select row_number() over () as rownum, * from test limit 10;
rownum | id | info | crt_time
--------+----+------+----------------------------
1 | 1 | test | 2018-01-24 11:06:24.882708
2 | 2 | test | 2018-01-24 11:06:24.882708
3 | 3 | test | 2018-01-24 11:06:24.882708
4 | 4 | test | 2018-01-24 11:06:24.882708
5 | 5 | test | 2018-01-24 11:06:24.882708
6 | 6 | test | 2018-01-24 11:06:24.882708
7 | 7 | test | 2018-01-24 11:06:24.882708
8 | 8 | test | 2018-01-24 11:06:24.882708
9 | 9 | test | 2018-01-24 11:06:24.882708
10 | 10 | test | 2018-01-24 11:06:24.882708
(10 rows)
3、LIMIT，直接语法支持
postgres=# select * from test limit 10;
id | info | crt_time
----+------+----------------------------
1 | test | 2018-01-24 11:06:24.882708
2 | test | 2018-01-24 11:06:24.882708
3 | test | 2018-01-24 11:06:24.882708
4 | test | 2018-01-24 11:06:24.882708
5 | test | 2018-01-24 11:06:24.882708
6 | test | 2018-01-24 11:06:24.882708
7 | test | 2018-01-24 11:06:24.882708
8 | test | 2018-01-24 11:06:24.882708
9 | test | 2018-01-24 11:06:24.882708
10 | test | 2018-01-24 11:06:24.882708
(10 rows)
4、为某个字段生成序列值。
postgres=# create temp sequence if not exists tmp_seq;
postgres=# alter sequence tmp_seq restart with 1;
postgres=# alter table test add column col1 int;
ALTER TABLE
postgres=# update test set col1=nextval('tmp_seq');
UPDATE 10000000
postgres=# select * from test limit 10;
id | info | crt_time | col1
----+------+----------------------------+------
1 | test | 2018-01-24 11:06:24.882708 | 1
2 | test | 2018-01-24 11:06:24.882708 | 2
3 | test | 2018-01-24 11:06:24.882708 | 3
4 | test | 2018-01-24 11:06:24.882708 | 4
5 | test | 2018-01-24 11:06:24.882708 | 5
6 | test | 2018-01-24 11:06:24.882708 | 6
7 | test | 2018-01-24 11:06:24.882708 | 7
8 | test | 2018-01-24 11:06:24.882708 | 8
9 | test | 2018-01-24 11:06:24.882708 | 9
10 | test | 2018-01-24 11:06:24.882708 | 10
(10 rows)
PPAS rownum
PG的商用发行版本PPAS完全实现了与Oracle一样的rownum功能。
小结
主要看业务到底用Oracle的rownum干什么，才能因地制宜，使用对应的PG语法来支持对应的场景。
当然，目前PG的商用发行版本PPAS（EnterpriseDB）的rownum功能已经完全和Oracle一样了。如果业务上要节约改造时间，使用PPAS是最好的选择。
