Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ Pseudocolumn
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Pseudocolumn

ROWNUM is a pseudocolumn that returns a number indicating the row number of the obtained query result. The value of ROWNUM in the first row is 1, the value of ROWNUM in the second row is 2, and so on. ROWNUM can be used to limit the number of rows returned by a query, as shown in the following example: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
gaussdb=# CREATE TABLE Students (name varchar(20), id int) with (STORAGE_TYPE = USTORE);
gaussdb=# INSERT INTO Students VALUES ('Jack', 35);
gaussdb=# INSERT INTO Students VALUES ('Leon', 15);
gaussdb=# INSERT INTO Students VALUES ('James', 24);
gaussdb=# INSERT INTO Students VALUES ('Taker', 81);
gaussdb=# INSERT INTO Students VALUES ('Mary', 25);
gaussdb=# INSERT INTO Students VALUES ('Rose', 64);
gaussdb=# INSERT INTO Students VALUES ('Perl', 18);
gaussdb=# INSERT INTO Students VALUES ('Under', 57);
gaussdb=# INSERT INTO Students VALUES ('Angel', 101);
gaussdb=# INSERT INTO Students VALUES ('Frank', 20);
gaussdb=# INSERT INTO Students VALUES ('Charlie', 40);

-- Output the first 10 rows of data records in the Students table.
gaussdb=# SELECT * FROM Students WHERE rownum <= 10; 
 name  | id  
-------+-----
 Jack  |  35
 Leon  |  15
 James |  24
 Taker |  81
 Mary  |  25
 Rose  |  64
 Perl  |  18
 Under |  57
 Angel | 101
 Frank |  20
(10 rows)
If the statement has a clause, the output rows are reordered according to the clause. 
1
2
3
4
5
6
7
8
 
gaussdb=# SELECT * FROM Students WHERE rownum < 5 order by 1;
 name  | id
-------+----
 Jack  | 35
 James | 24
 Leon  | 15
 Taker | 81
(4 rows)
If a subquery has a clause but the condition is placed in the outermost query, you can use the ROWNUM condition after sorting. 
1
2
3
4
5
6
 
gaussdb=# SELECT rownum, * FROM (SELECT * FROM Students order by 1) WHERE rownum <= 2;
 rownum |  name   | id
--------+---------+-----
      1 | Angel   | 101
      2 | Charlie |  40
(2 rows)
As long as ROWNUM is greater than a specific positive integer, the condition is always false. As shown in the following example, the statement does not return any result in the table: 
1
2
3
4
 
gaussdb=# SELECT * FROM Students WHERE rownum > 1;
 name | id
------+----
(0 rows)
Use ROWNUM to assign a value to each row within a certain range of the table. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
gaussdb=# SELECT * FROM Students;
  name   | id
---------+-----
 Jack    |  35
 Leon    |  15
 James   |  24
 Taker   |  81
 Mary    |  25
 Rose    |  64
 Perl    |  18
 Under   |  57
 Angel   | 101
 Frank   |  20
 Charlie |  40
(11 rows)

gaussdb=# UPDATE Students set id = id + 5 WHERE rownum < 4;
UPDATE 3
gaussdb=# SELECT * FROM Students;
  name   | id
---------+-----
 Jack    |  40
 Leon    |  20
 James   |  29
 Taker   |  81
 Mary    |  25
 Rose    |  64
 Perl    |  18
 Under   |  57
 Angel   | 101
 Frank   |  20
 Charlie |  40
(11 rows)

gaussdb=# DROP TABLE Students;
DROP TABLE
The restrictions on using ROWNUM are as follows:
  • Do not use ROWNUM as an alias to avoid ambiguity in SQL statements.
  • Do not use ROWNUM when creating an index.
  • Do not use ROWNUM as the default value when creating a table.
  • Do not use ROWNUM as an alias in the WHERE clause.
  • Do not use ROWNUM when inserting data.
  • Do not use ROWNUM in a tableless query.
  • Do not use ROWNUM in the LIMIT clause.
  • ROWNUM cannot be used as a parameter of the EXECUTE statement.
  • Do not use ROWNUM to update a clause in the UPSERT statement.
  • If the HAVING clause contains ROWNUM (not in an aggregate function), the GROUP BY clause must also contain ROWNUM (not in an aggregate function), unless the GROUP BY clause contains an expression, for example, SELECT a + a FROM t group by a + a having rownum < 5.
  • If the ROWNUM condition exists in the HAVING clause, the HAVING clause cannot be pushed down to any scan node. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    gaussdb=# CREATE TABLE test (a int, b int);
CREATE TABLE
gaussdb=# INSERT INTO test SELECT generate_series, generate_series FROM generate_series(1, 10);
INSERT 0 10

-- The rownum condition cannot be pushed down to seqscan.
gaussdb=# EXPLAIN SELECT a,rownum FROM test group by a,rownum having rownum < 5; 
                             QUERY PLAN                             
--------------------------------------------------------------------
 HashAggregate  (cost=42.23..69.10 rows=2149 width=4)
   Group By Key: a, ROWNUM
   Filter: ((ROWNUM) < 5)
   ->  Rownum  (cost=0.00..31.49 rows=2149 width=4)
         ->  Seq Scan on test  (cost=0.00..31.49 rows=2149 width=4)
(5 rows)
  • If a subquery contains the ROWNUM condition, the predicate cannot be pushed down to any scan node. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    -- b<5 cannot be pushed down to seqscan.
gaussdb=# EXPLAIN SELECT * FROM (SELECT * FROM test WHERE rownum < 5) WHERE b < 5; 
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Subquery Scan on __unnamed_subquery__  (cost=0.00..0.01 rows=1 width=8)
   Filter: (__unnamed_subquery__.b < 5)
   ->  Rownum  (cost=0.00..0.00 rows=1 width=8)
         StopKey: (ROWNUM < 5)
         ->  Seq Scan on test  (cost=0.00..31.49 rows=2149 width=8)
(5 rows)

gaussdb=# DROP TABLE test;
DROP TABLE

It is not recommended that the ROWNUM condition be used in the JOIN ON clause. In GaussDB, when the ROWNUM condition is used in the JOIN ON clause, the behavior in the LEFT JOIN, RIGHT JOIN, FULL JOIN, and MERGE INTO scenarios is different from that in other databases, causing risks in service migration.

If the parent query contains the rownum restriction and the projection column of the subquery contains rownum, the restriction is pushed down to the subquery. The constraints are as follows:

  • The parent query can be pushed down only when the rownum restriction condition is <, <=, or = and the subquery directly uses rownum as the pseudocolumn.
  • If the parent query has multiple filter criteria for rownum in the subquery and the pushdown requirements are met, only the first filter criterion is pushed down.
  • If a subquery contains volatile functions and stored procedures, the pushdown is not supported.
Parent topic: SQL Reference