sql - How to show row numbers in PostgreSQL query?

Question

I'd like to show the observation number for each record returned by a PostgreSQL query.

I think in 8.4 windowing functions can perform this capability.

Answer 1

select   row_number() over (order by <field> nulls last) as rownum, *
from     foo_tbl
order by <field>

If order is not necessary, this answer may also be simplified:

select row_number() over(), *  -- notice: no fields are needed
from   foo_tbl

---

SQL Fiddle Proof of Concept