Thread: empty set
PostgreSQL 8.1 I've been trying to write a SQL prepare routine. One of the challenging elements I'm running into is an empty set ... "select foo from bar where foo in ? ;" What if "?" is an set with zero elements? What is the proper value to use to replace "?" indicating an empty set? CG __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, 2006-06-08 at 16:40, CG wrote: > PostgreSQL 8.1 > > I've been trying to write a SQL prepare routine. One of the challenging > elements I'm running into is an empty set ... > > "select foo from bar where foo in ? ;" > > What if "?" is an set with zero elements? What is the proper value to use to > replace "?" indicating an empty set? NULL?
That will work if foo is never NULL. If foo takes on a NULL value you will get those records where it is NULL.
Is there a reason you cannot do an if/else statement on the list size? That is what I do on my queries.
-Aaron
Is there a reason you cannot do an if/else statement on the list size? That is what I do on my queries.
-Aaron
On 6/8/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Thu, 2006-06-08 at 16:40, CG wrote:
> PostgreSQL 8.1
>
> I've been trying to write a SQL prepare routine. One of the challenging
> elements I'm running into is an empty set ...
>
> "select foo from bar where foo in ? ;"
>
> What if "?" is an set with zero elements? What is the proper value to use to
> replace "?" indicating an empty set?
NULL?
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
On Thu, Jun 08, 2006 at 14:40:12 -0700, CG <cgg007@yahoo.com> wrote: > PostgreSQL 8.1 > > I've been trying to write a SQL prepare routine. One of the challenging > elements I'm running into is an empty set ... > > "select foo from bar where foo in ? ;" > > What if "?" is an set with zero elements? What is the proper value to use to > replace "?" indicating an empty set? Something like the following suggests you can put a subquery there that returns 0 rows. I don't think that wil work for prepared queries though; so it may not help you. bruno=> select * from test where test in (select 1 where false);test ------ (0 rows)
"select 1 where false" does indeed indicate an empty set. I was hoping for something more elegant, but I'll take what I can get. :) --- Bruno Wolff III <bruno@wolff.to> wrote: > On Thu, Jun 08, 2006 at 14:40:12 -0700, > CG <cgg007@yahoo.com> wrote: > > PostgreSQL 8.1 > > > > I've been trying to write a SQL prepare routine. One of the challenging > > elements I'm running into is an empty set ... > > > > "select foo from bar where foo in ? ;" > > > > What if "?" is an set with zero elements? What is the proper value to use > to > > replace "?" indicating an empty set? > > Something like the following suggests you can put a subquery there that > returns > 0 rows. I don't think that wil work for prepared queries though; so it may > not > help you. > > bruno=> select * from test where test in (select 1 where false); > test > ------ > (0 rows) > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com