From: | Mike Leahy <mgleahy(at)alumni(dot)uwaterloo(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Is it possible to summarize uniqe values from an indexed |
Date: | 2006-04-28 20:46:48 |
Message-ID: | BAY102-DAV168AA3E20F3A9C8A078784BCB20@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dany De Bontridder | 2006-04-28 22:00:15 | dump Functions |
Previous Message | Geoffrey | 2006-04-28 20:34:32 | Re: How to define + operator for strings |