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

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,
>
>