i got help with this from one of the developers on irc, thanks man,
sorry i forgot your handle:
(hope this is right)
select keyword, count(*)
from facts
where having count(*) > 1;
i think that is it, i dont have to table anymore,
building a better schemea and more stuff for everything,
but that was just sql ninja-ness man,
thanks,
Chris Humphries
ps -> open magazine (openmagazine.net) had an article in it that said
good things about postgresql btw, just in case interested...it
is a free subscription i think. and no this isnt spam, i dont
work for them or anything, just an observation...
On Sun, Feb 18, 2001 at 02:54:08AM -0800, Chris Humphries wrote:
> hello all,
>
> first off, some information:
> =====
> =====
> the table:
> Table "facts"
> Attribute | Type | Modifier
> -------------+--------------+----------
> keyword | varchar(80) |
> description | varchar(255) |
> url | varchar(255) |
>
>
> the sql:
> select keyword from facts as f1
> where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword)
> order by keyword;
>
> my system:
> Linux kernel 2.2.x
> p166
> 32M ram
> 200M+ swap
> =====
> =====
>
> it took about 65 minutes to complete. i know that it is doing alot of work,
> but it there a way that it could be sped up, like something i could configure
> or something that i could do to make it faster?
>
> just looking to see if the time it took to take this is to be expected?
> postmaster was using from 78-98% of the cpu for the whole time.
>
> in hind site i wish i would have made keyword not null primary key...
> *sigh*, learn from mistakes i guess. now have to fix the duplicates and
> move all the rows into a new table with keyword as primary key...
>
> thanks for any help,
> and developers: thanks for developing a real non-toy database for the masses,
>
> -chris humphries
>
>
>
>
>
>
>
>
>
>
