[SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)

Dear All,

I think Daniel's idea is brilliant. Why don't we set up some sort of
'postgreSQL cookbook' where everybody puts in their "tricks of the trade"
(for the moment I'm thinking mostly SQL related)?

A first pass of the FAQ should generate quite a few of these; like the
"SELECT my_index, count(my_index) from my_table group by my_index having
count(my_index) > 1" example below or Bruce's new FAQ item re: getting a
more efficient IN subquery equivalent.

Perhaps we could even have a (moderated?) newsgroup where we could cc:
cookbook like entries that appear in the other pgsql newsgroups.

Just ideas, for the moment I saddly have no time to make a start on this
(paper deadline looming ominously close) but I'm holding this thought!

Any comments?

Cheers,

Stuart.

On Tue, 28 Sep 1999, Bruce Momjian wrote:

> >
> > > as EXISTS, but by the time we tell them that, they have already spent
> > > much time trying to figure out why the query is so slow, and I am sure
> > > many people don't even know about the EXISTS workaround.
> >
> > You are right: I spend some time scratching my head, then some time
> > searching the mailing lists and I finally made the query with a EXISTS,
> > which works great for me :) Thanks
> > Can this be a candidate to include in the FAQ?
> >
> > On the same idea, is there any good document out there with all the SQL
> > "recipes" or common practice for things like : "Give me all the rows which
> > have this value in this column more than once, etc"
> > I do it with:
> > select my_index, count(my_index) from my_table group by my_index having
> > count(my_index) > 1;
> >
> New FAQ:
>
> 4.23) Why are my subqueries using IN so slow?
>

<snip - an entry which could also be put into a cookbook>

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7033
e-mail: rison(at)biochem(dot)ucl(dot)ac(dot)uk