pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: ALL() question
10+ messages / 5 participants
[nested] [flat]
* Re: ALL() question
@ 2007-11-14 11:56 Richard Huxton <dev@archonet.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2007-11-14 11:56 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-sql
Julien Cigar wrote:
>
> What I would like is a query that returns all the specimen_id of
> this table which have _all_ the given test_bit_id.
[snip]
> With the following I got a syntax error:
> select specimen_id
> from specimen_test_bits
> where test_bit_id = all(1,2,3,4);
It's expecting an array here. You'd have to write
= all('{1,2,3,4}')
But that would have the same problem as...
> The following works but no rows are returned :
> select specimen_id
> from specimen_test_bits
> where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
It's testing each row individually and of course one row can't match ALL
four values.
What you want to do is count the distinct values. Something like:
SELECT
specimen_id
FROM foo
GROUP BY
specimen_id
HAVING
count(distinct test_bit_id) = 4
;
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 12:57 Bart Degryse <Bart.Degryse@indicator.be>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 0 replies; 10+ messages in thread
From: Bart Degryse @ 2007-11-14 12:57 UTC (permalink / raw)
To: pgsql-sql
The doc says "The right-hand side is a parenthesized subquery, which must return exactly one column..."
That's what you have if using "... where test_bit_id = all(select id from test_bits where id in (1,2,3,4));"
The doc continues "...The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result ..."
So your where expression is equivalent to:
where test_bit_id = (select id from test_bits where id = 1) AND
test_bit_id = (select id from test_bits where id = 2) AND
test_bit_id = (select id from test_bits where id = 3) AND
test_bit_id = (select id from test_bits where id = 4);
The doc continues "... The result of ALL is "true" if all rows yield true ..."
Since test_bit_id can never be 1, 2, 3 and 4 at the same time the result of ALL will be false. So no records get returned.
>>> Julien Cigar <jcigar@ulb.ac.be> 2007-11-14 15:50 >>>
On Wed, 2007-11-14 at 11:56 +0000, Richard Huxton wrote:
> Julien Cigar wrote:
> >
> > What I would like is a query that returns all the specimen_id of
> > this table which have _all_ the given test_bit_id.
> [snip]
> > With the following I got a syntax error:
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(1,2,3,4);
>
> It's expecting an array here. You'd have to write
> = all('{1,2,3,4}')
> But that would have the same problem as...
>
> > The following works but no rows are returned :
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>
> It's testing each row individually and of course one row can't match ALL
> four values.
>
> What you want to do is count the distinct values. Something like:
>
> SELECT
> specimen_id
> FROM foo
> GROUP BY
> specimen_id
> HAVING
> count(distinct test_bit_id) = 4
> ;
>
I don't think it would work, for example if I have:
specimen_id | test_bit_id
------------+------------
100 1
100 3
101 1
101 2
the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..
from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 13:20 Richard Huxton <dev@archonet.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2007-11-14 13:20 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-sql
Julien Cigar wrote:
> On Wed, 2007-11-14 at 11:56 +0000, Richard Huxton wrote:
>> Julien Cigar wrote:
>>> What I would like is a query that returns all the specimen_id of
>>> this table which have _all_ the given test_bit_id.
>> [snip]
>>> With the following I got a syntax error:
>>> select specimen_id
>>> from specimen_test_bits
>>> where test_bit_id = all(1,2,3,4);
>> It's expecting an array here. You'd have to write
>> = all('{1,2,3,4}')
>> But that would have the same problem as...
>>
>>> The following works but no rows are returned :
>>> select specimen_id
>>> from specimen_test_bits
>>> where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>> It's testing each row individually and of course one row can't match ALL
>> four values.
>>
>> What you want to do is count the distinct values. Something like:
>>
>> SELECT
>> specimen_id
>> FROM foo
>> GROUP BY
>> specimen_id
>> HAVING
>> count(distinct test_bit_id) = 4
>> ;
>>
>
> I don't think it would work, for example if I have:
> specimen_id | test_bit_id
> ------------+------------
> 100 1
> 100 3
> 101 1
> 101 2
>
> the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
> would return specimen_id 101 too, which I don't want ...
Not if you test for what you want too:
...
FROM foo
WHERE test_bit_id = ANY ('{1,3}')
...or...
WHERE test_bit_id IN (1,3)
> What I would like is the specimen_id which match _exactly_ the given
> test_bit_ids, so it should return only 100 in this example ..
>
> from the documentation ALL() can take a subquery too, not only an ARRAY
> (http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
Yes, but that doesn't help with your query - one row can't match ALL
your values.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 13:21 Richard Huxton <dev@archonet.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2007-11-14 13:21 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-sql
Julien Cigar wrote:
> I finally found a solution:
>
> SELECT specimen_id
> FROM specimen_test_bits
> GROUP BY specimen_id
> HAVING array_accum(test_bit_id) = '{2,3,4}';
>
> .. but I don't think it's very "clean" ..
The key question is whether you can rely on getting (2,3,4) or whether
you might get (4,3,2) or some other ordering.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 13:31 hubert depesz lubaczewski <depesz@depesz.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2007-11-14 13:31 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-sql
On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote:
> With the following I got a syntax error:
> select specimen_id
> from specimen_test_bits
> where test_bit_id = all(1,2,3,4);
where test_bit_id in (1,2,3,4)
group by specimen_id
having count(distinct test_bit_id) = 4;
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
^ permalink raw reply [nested|flat] 10+ messages in thread
* ALL() question
@ 2007-11-14 13:39 Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 3 replies; 10+ messages in thread
From: Julien Cigar @ 2007-11-14 13:39 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a problem with the ALL() subquery expression.
I have three tables:
- specimens
- test_bits
- specimen_test_bits
The specimen_test_bits table contains two foreign keys, one to
specimens(id), another to test_bits(id).
Here is an output of specimen_test_bits:
muridae=> select * from specimen_test_bits;
specimen_id | test_bit_id
-------------+-------------
46096 | 1
46096 | 2
46096 | 3
46096 | 4
52894 | 1
52894 | 3
12546 | 2
What I would like is a query that returns all the specimen_id of
this table which have _all_ the given test_bit_id. So in this
case, with test_bit_id 1,2,3,4 it should return only
specimen_id 46096.
With the following I got a syntax error:
select specimen_id
from specimen_test_bits
where test_bit_id = all(1,2,3,4);
The following works but no rows are returned :
select specimen_id
from specimen_test_bits
where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
Any idea how I could do this ? I guess the problem is my ALL() expression ...
In advance thanks,
Julien
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 14:50 Julien Cigar <jcigar@ulb.ac.be>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 10+ messages in thread
From: Julien Cigar @ 2007-11-14 14:50 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Wed, 2007-11-14 at 11:56 +0000, Richard Huxton wrote:
> Julien Cigar wrote:
> >
> > What I would like is a query that returns all the specimen_id of
> > this table which have _all_ the given test_bit_id.
> [snip]
> > With the following I got a syntax error:
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(1,2,3,4);
>
> It's expecting an array here. You'd have to write
> = all('{1,2,3,4}')
> But that would have the same problem as...
>
> > The following works but no rows are returned :
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>
> It's testing each row individually and of course one row can't match ALL
> four values.
>
> What you want to do is count the distinct values. Something like:
>
> SELECT
> specimen_id
> FROM foo
> GROUP BY
> specimen_id
> HAVING
> count(distinct test_bit_id) = 4
> ;
>
I don't think it would work, for example if I have:
specimen_id | test_bit_id
------------+------------
100 1
100 3
101 1
101 2
the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..
from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 14:57 Julien Cigar <jcigar@ulb.ac.be>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 1 reply; 10+ messages in thread
From: Julien Cigar @ 2007-11-14 14:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
I finally found a solution:
SELECT specimen_id
FROM specimen_test_bits
GROUP BY specimen_id
HAVING array_accum(test_bit_id) = '{2,3,4}';
.. but I don't think it's very "clean" ..
what do you think ?
Thanks
On Wed, 2007-11-14 at 15:50 +0100, Julien Cigar wrote:
> On Wed, 2007-11-14 at 11:56 +0000, Richard Huxton wrote:
> > Julien Cigar wrote:
> > >
> > > What I would like is a query that returns all the specimen_id of
> > > this table which have _all_ the given test_bit_id.
> > [snip]
> > > With the following I got a syntax error:
> > > select specimen_id
> > > from specimen_test_bits
> > > where test_bit_id = all(1,2,3,4);
> >
> > It's expecting an array here. You'd have to write
> > = all('{1,2,3,4}')
> > But that would have the same problem as...
> >
> > > The following works but no rows are returned :
> > > select specimen_id
> > > from specimen_test_bits
> > > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
> >
> > It's testing each row individually and of course one row can't match ALL
> > four values.
> >
> > What you want to do is count the distinct values. Something like:
> >
> > SELECT
> > specimen_id
> > FROM foo
> > GROUP BY
> > specimen_id
> > HAVING
> > count(distinct test_bit_id) = 4
> > ;
> >
>
> I don't think it would work, for example if I have:
> specimen_id | test_bit_id
> ------------+------------
> 100 1
> 100 3
> 101 1
> 101 2
>
> the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
> would return specimen_id 101 too, which I don't want ...
> What I would like is the specimen_id which match _exactly_ the given
> test_bit_ids, so it should return only 100 in this example ..
>
> from the documentation ALL() can take a subquery too, not only an ARRAY
> (http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 15:46 Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
parent: Julien Cigar <jcigar@ulb.ac.be>
2 siblings, 0 replies; 10+ messages in thread
From: Osvaldo Rosario Kussama @ 2007-11-14 15:46 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-sql
Julien Cigar escreveu:
> Hello,
>
> I have a problem with the ALL() subquery expression.
> I have three tables:
> - specimens
> - test_bits
> - specimen_test_bits
>
> The specimen_test_bits table contains two foreign keys, one to
> specimens(id), another to test_bits(id).
>
> Here is an output of specimen_test_bits:
>
> muridae=> select * from specimen_test_bits;
> specimen_id | test_bit_id
> -------------+-------------
> 46096 | 1
> 46096 | 2
> 46096 | 3
> 46096 | 4
> 52894 | 1
> 52894 | 3
> 12546 | 2
>
> What I would like is a query that returns all the specimen_id of
> this table which have _all_ the given test_bit_id. So in this
> case, with test_bit_id 1,2,3,4 it should return only
> specimen_id 46096.
>
> With the following I got a syntax error:
> select specimen_id
> from specimen_test_bits
> where test_bit_id = all(1,2,3,4);
>
> The following works but no rows are returned :
> select specimen_id
> from specimen_test_bits
> where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>
> Any idea how I could do this ? I guess the problem is my ALL() expression ...
>
Unclear, but works...
SELECT DISTINCT stb.specimen_id FROM specimen_test_bits stb
WHERE NOT EXISTS (SELECT * FROM (VALUES (1), (2), (3) ,
(4)) AS foo(id)
WHERE NOT EXISTS (SELECT
stb1.test_bit_id FROM specimen_test_bits stb1
WHERE foo.id =
stb1.test_bit_id
AND
stb.specimen_id = stb1.specimen_id));
Osvaldo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ALL() question
@ 2007-11-14 15:50 Julien Cigar <jcigar@ulb.ac.be>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 10+ messages in thread
From: Julien Cigar @ 2007-11-14 15:50 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-sql
Thanks :) it works as expected
Julien
On Wed, 2007-11-14 at 14:31 +0100, hubert depesz lubaczewski wrote:
> On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote:
> > With the following I got a syntax error:
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(1,2,3,4);
>
> where test_bit_id in (1,2,3,4)
> group by specimen_id
> having count(distinct test_bit_id) = 4;
>
> depesz
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-11-14 15:50 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-14 13:39 ALL() question Julien Cigar <jcigar@ulb.ac.be>
2007-11-14 11:56 ` Richard Huxton <dev@archonet.com>
2007-11-14 14:50 ` Julien Cigar <jcigar@ulb.ac.be>
2007-11-14 12:57 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-11-14 13:20 ` Richard Huxton <dev@archonet.com>
2007-11-14 14:57 ` Julien Cigar <jcigar@ulb.ac.be>
2007-11-14 13:21 ` Richard Huxton <dev@archonet.com>
2007-11-14 13:31 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-11-14 15:50 ` Julien Cigar <jcigar@ulb.ac.be>
2007-11-14 15:46 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
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