Archive for May 5th, 2009
PostgreSQL: row numbers
Note: this article concerns PostgreSQL 8.3 and below.
PostgreSQL 8.4 introduces window functions.
Window function ROW_NUMBER() implements the functionality in question more efficiently.
ROWNUM is a very useful pseudocolumn in Oracle that returns the position of each row in a final dataset.
Upcoming PostgreSQL 8.4 will have this pseudocolumn, but as for now will we need a hack to access it.
The main idea is simple:
- Wrap the query results into an array
- Join this array with a
generate_series()so that numbers from 1 toarray_upper()are returned - For each row returned, return this number (as
ROWNUM) along the corresponding array member (which is the row from the original query)
Let's create a table with multiple columns of different datatypes, write a complex query and try to assign the ROWNUM to the query results:
Read the rest of this entry »
Subscribe in a reader