Querying Row IDs

Every table you create has an internally generated value that identifies each row in the table and provides a "reference" to that row. These row IDs can be decomposed into information that may be useful for monitoring database activity. For example, based on a row ID, you can find out which worker processed a particular row or where it is physically stored.

Important: Although row IDs are unique, they are not immutable. Row IDs may change over time. Therefore, they are useful only within the scope of a single SQL statement, and not within transaction blocks. It is possible for a given row to have a different row ID in between different statements within the same transaction. Also, there is no direct correlation between row IDs and the order in which rows were added to the table; recently inserted rows may have IDs that are lower than IDs for previously inserted rows.
You can select the rowid column from any table in the database. For example:
premdb=# select rowid,* from season order by rowid;
     rowid     | seasonid | season_name | numteams |      winners      
---------------+----------+-------------+----------+-------------------
 1571966418944 |        1 | 1992-1993   |       22 | Manchester United
 1571966418945 |        2 | 1993-1994   |       22 | Manchester United
 1571966418946 |        3 | 1994-1995   |       22 | Blackburn Rovers
 1571966418947 |        4 | 1995-1996   |       20 | Manchester United
 ...

Rows that have not yet been flushed to the column store also have valid row IDs, but they are displayed as negative values.

Note: You cannot select row IDs from external tables.
The following system functions provide ways to query and extract information from row IDs:

These functions accept either the string rowid as their input or a specific rowid value. In most cases you will use the string rowid to return the specific row ID for each row in a set of rows. (If you specify an individual row ID as the input to the function, make sure it is valid; the system does not detect row IDs that are not valid or do not exist.)

For example, the following query returns specific row IDs and row numbers for a set of rows in the match table:

premdb=# select rowid, sys.row_number_from_rowid(rowid),* from match where seasonid=21 limit 10;
      rowid       | row_number_from_rowid | seasonid |      matchday       | htid | atid | ftscore | htscore 
------------------+-----------------------+----------+---------------------+------+------+---------+---------
 1126011584388774 |                  7846 |       21 | 2013-02-23 00:00:00 |    2 |   52 | 2-1     | 1-0
 1126011584388775 |                  7847 |       21 | 2012-09-29 00:00:00 |    2 |   63 | 1-2     | 1-1
 1126011584388776 |                  7848 |       21 | 2013-04-16 00:00:00 |    2 |   67 | 0-0     | 0-0
 1126011584388777 |                  7849 |       21 | 2012-11-10 00:00:00 |    2 |   68 | 3-3     | 2-2
 1126011584388778 |                  7850 |       21 | 2013-01-30 00:00:00 |    2 |   73 | 2-2     | 0-1
 1126011584388779 |                  7851 |       21 | 2013-01-13 00:00:00 |    2 |   74 | 0-2     | 0-2
 1126011584388780 |                  7852 |       21 | 2013-04-28 00:00:00 |    2 |   75 | 1-1     | 1-1
 1126011584388781 |                  7853 |       21 | 2012-12-29 00:00:00 |    2 |   77 | 7-3     | 1-1
 1126011584388782 |                  7854 |       21 | 2013-04-13 00:00:00 |    2 |   78 | 3-1     | 0-0
 1126011584388783 |                  7855 |       21 | 2012-10-27 00:00:00 |    2 |   82 | 1-0     | 0-0
(10 rows)
The following example finds out where (on which worker node) the rows in the matchstats table are stored:
yellowbrick_test=# select sys.worker_id_from_rowid(rowid), count(*) 
from matchstats group by sys.worker_id_from_rowid(rowid) order by 1;
 worker_id_from_rowid | count 
----------------------+-------
                    0 | 24437
                    1 | 24244
                    2 | 24761
                    3 | 24361
                    4 | 24000
                    5 | 24348
                    6 | 24497
                    7 | 24270
                    8 | 24600
                    9 | 24665
                   10 | 24409
                   11 | 24359
                   12 | 24528
                   13 | 24176
                   14 | 24590
(15 rows)
To return the UUID for each worker instead of its logical ID, use the sys.worker_uuid function:
yellowbrick_test=# select sys.worker_uuid(sys.worker_id_from_rowid(rowid)), count(*) 
from crdm_giftcard group by sys.worker_id_from_rowid(rowid) order by sys.worker_id_from_rowid(rowid);
             worker_uuid              | count 
--------------------------------------+-------
 00000000-0000-0000-0000-38b8ebd00154 | 24437
 00000000-0000-0000-0000-38b8ebd000dc | 24244
 00000000-0000-0000-0000-38b8ebd002c6 | 24761
 00000000-0000-0000-0000-38b8ebd00069 | 24361
 00000000-0000-0000-0000-38b8ebd00041 | 24000
 00000000-0000-0000-0000-38b8ebd00091 | 24348
 00000000-0000-0000-0000-38b8ebd000aa | 24497
 00000000-0000-0000-0000-38b8ebd003c5 | 24270
 00000000-0000-0000-0000-38b8ebd0009b | 24600
 00000000-0000-0000-0000-38b8ebd00997 | 24665
 00000000-0000-0000-0000-38b8ebd002bc | 24409
 00000000-0000-0000-0000-38b8ebd003a2 | 24359
 00000000-0000-0000-0000-38b8ebd00028 | 24528
 00000000-0000-0000-0000-38b8ebd00212 | 24176
 00000000-0000-0000-0000-38b8ebd0025d | 24590
(15 rows)