3.2.9.2. PostgreSQL
3.2.9.2.1. 层次查询伪列
层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 CONNECT BY 子句。
3.2.9.2.1.1. LEVEL 伪列
LEVEL 伪列用来协助标记节点的层次。
在层次结构中，根为第 1 层，根的子结点为第 2 层，之后以此类推。例如，根节点的 LEVEL 值会返回 1，根节点的子节点的 LEVEL 值会返回 2，之后以此类推。
以四层级倒置树结构为例，Root Row 是倒置树中最高的行，LEVEL 值一般为 1。Child Row 是任何非 Root Row，LEVEL 值一般为 2、3 或 4。Parent Row 是任何有 Child Row 的行（Root Row 除外），LEVEL 值一般为 2 或 3。Leaf Row 是任何没有孩子节点的行，LEVEL 值一般为 4。
-- 转换前Oracle SQL: -- 构造列数 select level from dual connect by level<=10; LEVEL| -----+ 1| 2| 3| 4| 5| 6| 7| 8| 9| 10| -- 转换后PostgreSQL SQL: WITH RECURSIVE tmp AS (SELECT 1 AS "$level" UNION ALL SELECT "$level"+1 AS "$level" FROM tmp WHERE "$level"+1<=10) SELECT tmp."$level" AS level FROM tmp level| -----+ 1| 2| 3| 4| 5| 6| 7| 8| 9| 10|
3.2.9.2.2. 序列伪列
序列（Sequence）是数据库按照一定规则生成的自增数字序列。因其自增的特性，通常被用作主键和唯一键。本节主要介绍序列的取值方法和应用场景。
3.2.9.2.2.1. 序列的取值方法
可以使用如下伪列引用 SQL 语句中的序列值：
CURRVAL：返回序列的当前值。
NEXTVAL：返回序列的下一个自增值。
使用序列伪列时，必须在 CURRVAL 和 NEXTVAL 前带上序列的名称，并用句点（.）引用。例如，序列的名称为 SEQ_FOO，则可以通过 SEQ_FOO.CURRVAL 获取 SEQ_FOO 序列的当前值。同样，可以通过 SEQ_FOO.NEXTVAL 获取 SEQ_FOO 序列的下一个自增值。
3.2.9.2.2.2. 序列值的应用场景
通过 CURRVAL 和 NEXTVAL 引用的序列值可以用于以下位置：
非子查询或者视图中的 SELECT 语句的选择列表中。
INSERT 语句中子查询的选择列表中。
INSERT 语句中的 VALUE 子句中。
UPDATE 语句中的 SET 子句中。
序列 CURRVAL 和 NEXTVAL 的值不能用于以下位置：
DELETE、SELECT 或者 UPDATE 语句的子查询中。
视图的查询中。
带 DISTINCT 运算符的 SELECT 语句中。
带 GROUP BY 子句或者 ORDER BY 子句的 SELECT 语句中。
与另一个 SELECT 语句通过 UNION、INTERSECT 或者 MINUS 集合运算符进行联合的 SELECT 语句中。
SELECT 语句的 WHERE 子句中。
CREATE TABLE 或者 ALTER TABLE 语句中列的 DEFAULT 值。
CHECK 约束的条件中。
-- 创建sequence create sequence uni_seq increment by 1 start with 1; -- 转换前Oracle SQL： -- 获取下一个sequence的值 SELECT uni_seq.nextval FROM unisql_employee ke; NEXTVAL| -------+ 1| 2| 3| 4| 5| 6| -- 转换后PostgreSQL 创建sequence SQL： CREATE SEQUENCE uni_seq INCREMENT BY 1 START WITH 1 -- 转换后PostgreSQL SQL： SELECT nextval('uni_seq') FROM unisql_employee AS ke nextval| -------+ 1| 2| 3| 4| 5| 6|
3.2.9.2.2.3. 使用注意事项
当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。
-- 如果表结构定义如下：
DROP TABLE unisql_key_test_2;
CREATE TABLE unisql_key_test_2(
id NUMBER,
currval varchar(10),
nextval varchar(10)
);
INSERT INTO unisql_key_test_2(currval,nextval) values('currval1','nextval1');
-- 同时使用不支持,转换结果可能和预期不一致。
SELECT unisql_seq_1.nextval,nextval,unisql_seq_1.currval,currval FROM unisql_key_test_2;
-- 推荐使用方式：
-- 如果需要同时使用，表字段请用双引号包裹起来
CREATE TABLE unisql_key_test_3(
id NUMBER,
"currval" varchar(10),
"nextval" varchar(10)
);
INSERT INTO unisql_key_test_3("currval","nextval") values('currval1','nextval1');
SELECT unisql_seq_1.nextval,"nextval",unisql_seq_1.currval,"currval" FROM unisql_key_test_3;
-- 注意以下语句转换会得到和预期不一样的结果
SELECT unisql_seq_1.nextval,unisql_seq_1.lnextval,unisql_seq_1.currval,unisql_seq_1.currval FROM unisql_key_test_1 unisql_seq_1;
3.2.9.2.3. ROWID 伪列
ROWID 伪列提供了快速定位表中某一行的能力，ROWID 值由主键编码得到，不会进行实际的存储。
-- 转换前Oracle SQL： SELECT ROWID,ke.* FROM unisql_employee ke; ROWID |EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT| ------------------+-----------+-----+------+-------------+----------+--------------+ AAA2fQAAMAAIbD0AAA| 1|JACK | 5000| 1|2023-01-01| 0.05| AAA2fQAAMAAIbD0AAB| 2|TOM | 10000| 1|2023-02-01| 0.15| AAA2fQAAMAAIbD0AAC| 3|LINDA| 15000| 1|2023-03-01| 0.2| AAA2fQAAMAAIbD0AAD| 4|ADA | 20000| 2|2023-04-01| 0.1| AAA2fQAAMAAIbD0AAE| 5|TINA | 30000| 2|2023-05-01| 0.2| AAA2fQAAMAAIbD0AAF| 6|KATE | 50000| 3|2023-06-01| 0.3| -- 转换后PostgreSQL SQL： SELECT CTID,ke.* FROM unisql_employee AS ke rowid|employee_id|name |salary|department_id|hire_date |commission_pct| -----+-----------+-----+------+-------------+----------+--------------+ (0,1)| 1|JACK | 5000| 1|2023-01-01| 0.05| (0,2)| 2|TOM | 10000| 1|2023-02-01| 0.15| (0,3)| 3|LINDA| 15000| 1|2023-03-01| 0.2| (0,4)| 4|ADA | 20000| 2|2023-04-01| 0.1| (0,5)| 5|TINA | 30000| 2|2023-05-01| 0.2| (0,6)| 6|KATE | 50000| 3|2023-06-01| 0.3|
3.2.9.2.4. ROWNUM 伪列
ROWNUM 伪列会对查询结果中的每一行进行编号，其值为该行在查询结果集中的具体位置。例如，第一行返回值 1，第二行返回值 2，之后以此类推。
统一SQL 支持以下ROWNUM的用法：
-- 转换前Oracle SQL: SELECT ke.*, ROWNUM AS row_num FROM unisql_employee ke; EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|ROW_NUM| -----------+-----+------+-------------+----------+--------------+-------+ 1|JACK | 5000| 1|2023-01-01| 0.05| 1| 2|TOM | 10000| 1|2023-02-01| 0.15| 2| 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| 5|TINA | 30000| 2|2023-05-01| 0.2| 5| 6|KATE | 50000| 3|2023-06-01| 0.3| 6| -- 转换后PostgreSQL SQL: SELECT ke.*,row_number() OVER () AS row_num FROM unisql_employee AS ke employee_id|name |salary|department_id|hire_date |commission_pct|row_num| -----------+-----+------+-------------+----------+--------------+-------+ 1|JACK | 5000| 1|2023-01-01| 0.05| 1| 2|TOM | 10000| 1|2023-02-01| 0.15| 2| 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| 5|TINA | 30000| 2|2023-05-01| 0.2| 5| 6|KATE | 50000| 3|2023-06-01| 0.3| 6| -- 转换前Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee ke WHERE rownum < 5; ROWNUM|EMPLOYEE_ID|SALARY| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| -- 转换后PostgreSQL SQL: SELECT row_number() OVER () AS "ROWNUM",employee_id,SALARY FROM unisql_employee AS ke LIMIT 5-1 rownum|employee_id|salary| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| -- 转换前Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee ke WHERE rownum <= 5; ROWNUM|EMPLOYEE_ID|SALARY| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| 5| 5| 30000| -- 转换后PostgreSQL SQL: SELECT row_number() OVER () AS "ROWNUM",employee_id,SALARY FROM unisql_employee AS ke LIMIT 5 rownum|employee_id|salary| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| 5| 5| 30000| -- 转换前Oracle SQL: SELECT employee_id,SALARY FROM unisql_employee ke WHERE rownum = 1; EMPLOYEE_ID|SALARY| -----------+------+ 1| 5000| -- 转换后PostgreSQL SQL: SELECT employee_id,SALARY FROM unisql_employee AS ke LIMIT 1 employee_id|salary| -----------+------+ 1| 5000| -- 转换前Oracle SQL: SELECT * FROM (SELECT row_.*,rownum rownum_ FROM unisql_employee row_) WHERE rownum_ > 2 AND rownum_ <= 4; EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|ROWNUM_| -----------+-----+------+-------------+----------+--------------+-------+ 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| -- 转换后PostgreSQL SQL: SELECT * FROM (SELECT row_.*,row_number() OVER () AS rownum_ FROM unisql_employee AS row_) AS uni_sub WHERE rownum_>2 AND rownum_<=4 employee_id|name |salary|department_id|hire_date |commission_pct|rownum_| -----------+-----+------+-------------+----------+--------------+-------+ 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| -- 转换前Oracle SQL: SELECT * FROM (SELECT row_.*,rownum rownum_ FROM (select employee_id,salary,name from unisql_employee) row_) WHERE rownum_ > 2 AND rownum_ <= 4; EMPLOYEE_ID|SALARY|NAME |ROWNUM_| -----------+------+-----+-------+ 3| 15000|LINDA| 3| 4| 20000|ADA | 4| -- 转换后PostgreSQL SQL: SELECT * FROM (SELECT row_.*,row_number() OVER () AS rownum_ FROM (SELECT employee_id,salary,name FROM unisql_employee) AS row_) AS uni_sub WHERE rownum_>2 AND rownum_<=4 employee_id|salary|name |rownum_| -----------+------+-----+-------+ 3| 15000|LINDA| 3| 4| 20000|ADA | 4|