pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedempty set
5+ messages / 4 participants
[nested] [flat]
* empty set
@ 2006-06-08 21:40 CG <cgg007@yahoo.com>
2006-06-08 21:42 ` Re: empty set Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-09 07:33 ` Re: empty set Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: CG @ 2006-06-08 21:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: empty set
2006-06-08 21:40 empty set CG <cgg007@yahoo.com>
@ 2006-06-08 21:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-09 04:47 ` Re: empty set Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2006-06-08 21:42 UTC (permalink / raw)
To: cgg007@yahoo.com; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: empty set
2006-06-08 21:40 empty set CG <cgg007@yahoo.com>
2006-06-08 21:42 ` Re: empty set Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-06-09 04:47 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 5+ messages in thread
From: Aaron Bono @ 2006-06-09 04:47 UTC (permalink / raw)
To: pgsql-sql
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
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: empty set
2006-06-08 21:40 empty set CG <cgg007@yahoo.com>
@ 2006-06-09 07:33 ` Bruno Wolff III <bruno@wolff.to>
2006-06-09 13:00 ` Re: empty set CG <cgg007@yahoo.com>
1 sibling, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2006-06-09 07:33 UTC (permalink / raw)
To: CG <cgg007@yahoo.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: empty set
2006-06-08 21:40 empty set CG <cgg007@yahoo.com>
2006-06-09 07:33 ` Re: empty set Bruno Wolff III <bruno@wolff.to>
@ 2006-06-09 13:00 ` CG <cgg007@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: CG @ 2006-06-09 13:00 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-06-09 13:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-08 21:40 empty set CG <cgg007@yahoo.com>
2006-06-08 21:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-09 04:47 ` Aaron Bono <postgresql@aranya.com>
2006-06-09 07:33 ` Bruno Wolff III <bruno@wolff.to>
2006-06-09 13:00 ` CG <cgg007@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox