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:

    
     
       
       
         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 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.

    
         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.

    
         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:

    
         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.

    
     
       
       
         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.
Do not use ROWNUM as a parameter of the EXECUTE statement.
Do not use ROWNUM to update a clause in the UPSERT statement.
Do not use ROWNUM as a projection column or WHERE condition in the SELECT ... FOR UPDATE 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.

      
       
         
         
           gaussdb=# CREATE TABLE test (a int, b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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=3.45..3.49 rows=3 width=4)
   Group By Key: a, ROWNUM
   Filter: ((ROWNUM) < 5::numeric)
   ->  Rownum  (cost=0.19..3.40 rows=10 width=4)
         ->  Streaming (type: GATHER)  (cost=0.19..3.40 rows=10 width=4)
               Node/s: All datanodes
               ->  Seq Scan on test  (cost=0.00..3.03 rows=10 width=4)
(7 rows)                         

          

        

      
     

If a subquery contains the ROWNUM condition, the predicate cannot be pushed down to any scan node.

      
           gaussdb=# EXPLAIN SELECT * FROM (SELECT * FROM test WHERE rownum < 5) WHERE b < 5; -- The b<5 WHERE clause cannot be pushed down to Seq Scan.
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=0.06..0.63 rows=2 width=8)
   Node/s: All datanodes
   ->  Subquery Scan on __unnamed_subquery__  (cost=0.00..0.50 rows=2 width=8)
         Filter: (__unnamed_subquery__.b < 5)
         ->  Rownum  (cost=0.00..0.45 rows=4 width=8)
               StopKey: (ROWNUM < 5::numeric)
               ->  Streaming(type: BROADCAST)  (cost=0.00..1.36 rows=12 width=8)
                     Spawn on: All datanodes
                     ->  Rownum  (cost=0.00..1.35 rows=4 width=8)
                           StopKey: (ROWNUM < 5::numeric)
                           ->  Seq Scan on test  (cost=0.00..3.03 rows=10 width=8)
(11 rows)

gaussdb=# DROP TABLE test;
DROP TABLE

The query result of ROWNUM may be unstable due to the sequence in which the CN receives DN data.

        
         
           
           
             gaussdb=# CREATE TABLE test(a int, b int);
gaussdb=# INSERT INTO test VALUES(generate_series(1,10),generate_series(1,10));

-- The CN receives data from DN1 and then from DN2.
gaussdb=# SELECT rownum,* FROM test; 
 rownum | a  | b  
--------+----+----
      1 |  1 |  1
      2 |  2 |  2
      3 |  5 |  5
      4 |  6 |  6
      5 |  8 |  8
      6 |  9 |  9
      7 |  3 |  3
      8 |  4 |  4
      9 |  7 |  7
     10 | 10 | 10
(10 rows)

-- The CN receives data from DN2 and then from DN1.
gaussdb=# SELECT rownum,* FROM test; 
 rownum | a  | b  
--------+----+----
      1 |  3 |  3
      2 |  4 |  4
      3 |  7 |  7
      4 | 10 | 10
      5 |  1 |  1
      6 |  2 |  2
      7 |  5 |  5
      8 |  6 |  6
      9 |  8 |  8
     10 |  9 |  9
(10 rows)

            

          

        
       

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

Previous topic: Time Interval Expressions

Next topic: Type Conversion