ROWID equivalent in postgres 9.2

Question

Is there any way to get rowid of a record in postgres??

In oracle i can use like

SELECT MAX(BILLS.ROWID) FROM BILLS   

Answer 1

Yes, there is ctid column which is equivalent for rowid. But is useless for you. Rowid and ctid are physical row/tuple identifiers => can change after rebuild/vacuum.

See: Chapter 5. Data Definition > 5.4. System Columns

Answer 2

The PostgreSQL row_number() window function can be used for most purposes where you would use rowid. Whereas in Oracle the rowid is an intrinsic numbering of the result data rows, in Postgres row_number() computes a numbering within a logical ordering of the returned data. Normally if you want to number the rows, it means you expect them in a particular order, so you would specify which column(s) to order the rows when numbering them:

select client_name, row_number() over (order by date) from bills; 

If you just want the rows numbered arbitrarily you can leave the over clause empty:

select client_name, row_number() over () from bills; 

If you want to calculate an aggregate over the row number you'll have to use a subquery:

select max(rownum) from (     select row_number() over () as rownum from bills ) r; 

If all you need is the last item from a table, and you have a column to sort sequentially, there's a simpler approach than using row_number(). Just reverse the sort order and select the first item:

select * from bills  order by date desc limit 1;