PostgreSQL Oracle 兼容性之

2018-01-30 10:45:57来源:oschina作者:mcy0425人点击

分享
第七城市th7cn
标签

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 TABLEpostgres=# update test set col1=nextval('tmp_seq');
UPDATE 10000000postgres=# 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

https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Developers_Guide.1.021.html#pID0E0AEK0HA


PG的商用发行版本PPAS完全实现了与Oracle一样的rownum功能。


小结

主要看业务到底用Oracle的rownum干什么,才能因地制宜,使用对应的PG语法来支持对应的场景。


当然,目前PG的商用发行版本PPAS(EnterpriseDB)的rownum功能已经完全和Oracle一样了。如果业务上要节约改造时间,使用PPAS是最好的选择。


参考

https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Developers_Guide.1.021.html#pID0E0AEK0HA


阅读原文http://click.aliyun.com/m/41124/

第七城市th7cn

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台