ROWID equivalent in postgres 9.2

Question

Is there a way to get rowid entries in postgres ??

In oracle i can use as

SELECT MAX(BILLS.ROWID) FROM BILLS 

Answer 1

Yes, there is a ctid column that is equivalent for rowid. But it is useless to you. Rowid and ctid are physical identifiers of lines / tuples => may change after rebuilding / vacuum.

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

Answer 2

The PostgreSQL window function row_number () can be used for most purposes where you would use rowid. While in Oracle rowid is the internal row numbering of the resulting data, in Postgres row_number () calculates the numbering within the logical order of the returned data. Usually, if you want to number the rows, this means that you expect them in a certain order, so you must specify which column (s) to order the rows when they are numbered:

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

If you just want the lines to be numbered arbitrarily, you can leave the over clause empty:

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

If you want to calculate the aggregate by line number, you need to use a subquery:

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

If all you need is the last element of the table, and you have a column for sequential sorting, then there is a simpler approach than using row_number (). Just change the sort order and select the first item:

 select * from bills order by date desc limit 1; 

Answer 3

Use sequence. You can select 4 or 8 byte values.

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

Answer 4

Add any column to the table (the name may be rowid). And don't modify it by creating a trigger BEFORE UPDATE TRANSFER, which will throw an exception if someone tries to update. You can populate this column with the sequence as indicated by @JohnMudd.

Answer 5

 SELECT MAX(ctid) FROM table_name;