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  

Oppen

Sorry, but oracle ROWID does not give you the row number. – Acrophobia

What will be the purpose of this ROWID? – Thy

I need to get the latest row(last record) in a table. For this i am using sub query like SELECT * FROM BILLS WHERE ROWID=(SELECT MAX(BILLS.ROWID) FROM BILLS) – Oppen

The row with the highest rowid is NOT (I repeat NOT) guaranteed to be the "latest" row. That is a completely wrong assumption. – Keratose

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

Bibbs

ROWID is most certainly not useless. Because it is the physical identifier of a row's location it is the fastest possible access to that row (faster than a PK lookup). Certain batch operations can be sped up significantly by dumping ROWIDs then using them as update keys. Even more usefully, ROWID can be used in de-dupe deletes to choose among otherwise identical rows, e.g.: delete from sometable where rowid not in (select min(rowid) from sometable group by some, columns, that, should, be, unique); That ROWID and CTID are mutable over time is a valid caution but doesn't make them "useless". – Synchronism

I use ROWID all the time but only in the context of a single transaction. – Zimmerman

@NoahYetter: to be fair: iddqd said "useless for you" (addressing the OP) and I guess he/she was referring to the fact that jobi88 assumes that the rowid defines some kind of order of the rows. The rowid is most certainly "useless" to find the "latest row" – Keratose

ROWID is certainly not useless. I used it to delete duplicates in tables that do not have a primary key or a unique key, especially when I am preparing the table to build a PK or unique key using the existing columns. So CTID most likely can be used for the same. – Bawdy

@Guasqueño I was Postgresql contributor nad was working a lot with Postgresql and I am super curious. Question was "I need to get the latest row(last record) in a table." Could you please tell me how to use CTID for that? – Bibbs

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;

Fermium

I think you confused rowid with rownum. – Storeroom

You're right. To clarify, in Oracle, rowid is the physical location of the row. It has nothing to do with the order of insertion and cannot be used to identify the most recently inserted record. The closest equivalent in PostgreSQL would be the ctid. In Oracle the rownum is the position of the row in the result set, which can be emulated in PostgreSQL using the row_number() function as I described. – Fermium

This is the right answer if you want to have your rows numbered in postgres according to an order condition. See https://mcmap.net/q/66571/-sql-rank-versus-row_number and postgresql.org/docs/12/functions-window.html – Erlandson

Answer 3

Use a Sequence. You can choose 4 or 8 byte values.

http://www.neilconway.org/docs/sequences/

Blagoveshchensk

Answer 4

Add any unique column to your table(name maybe rowid). And prevent changing it by creating BEFORE UPDATE trigger, which will raise exception if someone will try to update. You may populate this column with sequence as @JohnMudd mentioned.

Quartile