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

Answer 2

Postgres < 8.4

For versions prior to 8.4:

SELECT    count(*) rownum, foo.*
FROM      datatable foo
JOIN      datatable bar
          ON (foo.pk_id <= bar.pk_id)
GROUP BY  foo.pk_id, foo.a, foo.b
ORDER BY  rownum
;

-- if there isn't a single unique/primary key field, you can concatenate fields
--    Example: ON (foo.a||foo.b||foo.c <= bar.a||bar.b||bar.c)

Hope this helps someone.

SQL Fiddle Proof of Concept

---

Another Approach

I suggest avoiding this at all cost, but wanted to include it for posterity. It is somewhat expensive and I imagine does not scale well, but when a primary key does not exist on a table (bad db-design), your options may be limited. In most of those cases the recommendation is to perform numbering at the application layer.

-- Based on basic table w/o primary key
-- CREATE TABLE names ( name as text );

SELECT num, name[num]
FROM (
  select generate_series( 1, (select count(*) from names) ) as num
) _nums,
(
  select array_agg(name) as name from names
) _names

SQL Fiddle Proof of Concept

Reasons it doesn't scale:

• packing your tuples into an array for each row is a bad idea