Thread: aggregate functions, COUNT
I'm currently using a SELECT count(*) when all I really want to know is if 1 or more records exist. Is there a standard way to just find out if a record exists? If not, is there a way to avoid iterating over all the records by writing an aggregate function? Given what I've read of how they work, I don't see how to make the function return before parsing all the results anyway, am I wrong here? Thanks for any help, or 2x4s, Kevin Way -- Kevin Way <kevin.way@overtone.org> http://www.overtone.org/
On Tue, 2 Oct 2001, Kevin Way wrote: > I'm currently using a SELECT count(*) when all I really want to know is > if 1 or more records exist. Is there a standard way to just find out if > a record exists? If not, is there a way to avoid iterating over all the > records by writing an aggregate function? Given what I've read of how > they work, I don't see how to make the function return before parsing > all the results anyway, am I wrong here? I think you could use EXISTS for that, select EXISTS (<query>); should give a true/false on whether the query returned any rows. I'm not sure if it stops after one row or not, but if it doesn't you can add a limit 1 to the query.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > I think you could use EXISTS for that, > select EXISTS (<query>); should give a true/false on whether the > query returned any rows. I'm not sure if it stops after one row > or not, but if it doesn't you can add a limit 1 to the query. Yes it does stop after one row; and furthermore, the planner knows to generate a fast-start plan for it. (Or at least it's supposed to ... hmm, this seems to be broken in current sources ...) Anyway, there's no need for LIMIT 1 inside an EXISTS, because the planner assumes that automatically. regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [02-10-01 18:02]: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > I think you could use EXISTS for that, > > select EXISTS (<query>); should give a true/false on whether the > > query returned any rows. I'm not sure if it stops after one row > > or not, but if it doesn't you can add a limit 1 to the query. > > Yes it does stop after one row; and furthermore, the planner knows to > generate a fast-start plan for it. (Or at least it's supposed to > ... hmm, this seems to be broken in current sources ...) Anyway, > there's no need for LIMIT 1 inside an EXISTS, because the planner > assumes that automatically. Thank you muchly, I did some profiling and SELECT EXISTS is indeed exactly what I wanted. -Kevin Way -- Kevin Way <kevin.way@overtone.org> http://www.overtone.org/