36

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

In oracle i can use like

SELECT MAX(BILLS.ROWID) FROM BILLS
jobi88
  • 3,865
  • 8
  • 21
  • 15

4 Answers4

26

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

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
baklarz2048
  • 10,699
  • 2
  • 31
  • 37
  • 29
    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". – Noah Yetter Nov 08 '13 at 18:09
  • 2
    I use ROWID all the time but only in the context of a single transaction. – Andrew Wolfe Apr 02 '15 at 21:14
  • 9
    @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" –  Apr 13 '17 at 12:37
  • 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. – Guasqueño Feb 23 '23 at 14:46
  • @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? – baklarz2048 Feb 27 '23 at 07:09
15

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;
Oliver Crow
  • 328
  • 2
  • 7
  • 5
    I think you confused rowid with rownum. – D. Mika May 29 '19 at 06:37
  • 1
    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. – Oliver Crow May 30 '19 at 08:59
  • This is the right answer if you want to have your rows numbered in postgres according to an order condition. See https://stackoverflow.com/questions/7747327/sql-rank-versus-row-number and https://www.postgresql.org/docs/12/functions-window.html – Michael S Jan 06 '21 at 15:05
0

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

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

JohnMudd
  • 13,607
  • 2
  • 26
  • 24
0

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.

Zaur Hajili
  • 29
  • 1
  • 2