Re: Is it possible to summarize uniqe values from an indexed

[Mike Leahy <mgleahy@xxxxxxxxxxxxxxxxxxx>]

Martijn,

This works rather well - especially in my case, where I have thousands of rows in my tables with only a handful of unique values in the year column.  I'm not sure if I can get it to work in pl/pgsql as a function (I'll give it a shot), but it'll be no problem for me to add a routine in my php scripts that does essentially the same thing.

I really appreciate your help,
Mike

Martijn van Oosterhout wrote:

On Fri, Apr 28, 2006 at 08:35:08AM -0400, Mike Leahy wrote:
  

Hello list,

Following from a question I had yesterday, I'm wondering if there is
some way to summarize the unique values of an indexed column in
PostgreSQL without having the query scan the whole table.  For my
current work, I have many large tables, all of which have an indexed
column for the year from which each row of data was recorded.  This year
column contains a small number of unique values (e.g., where a large
table contains data from one, two or three years...so far).  I've been
getting the unique values by executing queries like 'select distinct
year from [table];', or 'select year from [table] group by year;'.
    

I don't know if you can specify it in plain SQL, but you might be able
to code the following into a pl/pgsql function (this is pseudo-code):

function getunique
   $curr = "";
loop:
   select $next from table where field > $curr order by field limit 1;
   if found then
     return $next
     $curr = $next;
     goto loop;
   return;

The idea being that you ask the index lookup to find the next biggest
item. You end up doing lots of little queries but on big table with
lots of duplicates it might be a win.

Hope this helps,