Thread: ALL() question
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
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
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)
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)
>
>
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);
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
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
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
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)
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 >
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