1

oracle has something called row id which nothing but a Pseudo column, is there any alternative of that present in postgres and EDB. if yes that what is that and if not how I can solve this issue.

for refernce

SELECT /*+ PARALLEL(2) FULL(t) */ 
rowid, account_id, service_id, charge_date
FROMemployee %s t;
Biswajeet Sarkar
  • 13
  • 3
  • Whyt is it required anyway?? rowid is just a simple hexedecimal pointer to a location.If the you have asked for rownum that would have been considered.On the other hand you can use rank() over partition by to get rownums in postgres. – Nikhil S Jan 25 '22 at 13:06

2 Answers2

1

The correct replacement is the primary key of the table.

PostgreSQL does have an equivalent to Oracle's ROWID, which is ctid, but owing to the different implementation, that value changes with every update and cannot be used to reliably identify a table row across different SQL statements.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Oracle's `ROWID` can change (one example is if you have row-movement enabled) but it should be able to reliably identify rows within a single transaction. You should not rely on it across different transactions though. – MT0 Jan 25 '22 at 09:20
  • @MT0 Yes, I know. But row movement is disabled by default, right? So while using `ROWID`s across transactions may be slightly unsafe in Oracle, it definitely won't work with `ctid` in PostgreSQL. – Laurenz Albe Jan 25 '22 at 11:40
0

This is a simple workaround for it:

select rank() over (partition by <primary key> order by <primary key>) as rn,* 
from <tablename>;
Nikhil S
  • 3,786
  • 4
  • 18
  • 32