EXPLAIN EXTENDED

How to create fast database queries

Archive for May 5th, 2009

PostgreSQL: row numbers

with 2 comments

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:

  1. Wrap the query results into an array
  2. Join this array with a generate_series() so that numbers from 1 to array_upper() are returned
  3. 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 »

Written by Quassnoi

May 5th, 2009 at 11:00 pm

Posted in PostgreSQL