The rownum in Oracle is a Pseudo column, we can use it in select query. Similarly it can be used in update query as well with caution. Oracle works fine with rownum while it is used in update query like below. Note, rownum is not my favorite thing but can’t avoid seeing them while migrating oracle bound application to Postgres.
Here is the blog on rownum and limits which explains problem that you need to be aware while using rownum
update stats_table set lock_records=1,lock_date=sysdate, last_updated=sysdate where id in ('1') and rownum < 10;The PostgreSQL equivalent is Limit and Offset. For example, the above query can be written something like below,
update stats_table set lock_records=1, lock_date=sysdate(), last_updated=sysdate() where stats_table_id in (select stats_table_id from stats_table where id in ("1") and LIMIT 10);Refer sql-select link and comment what is the best ANSI Standard replacement for rownum?