Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found myself writing the following:

select 'yes' 
where exists(select * from foo where val=1)
and not exists(select * from foo where val<>1);

and wondering if there is a more concise way without sacrificing too much readability.

I found one way which I am posting as an answer but I'm not entirely happy with it and would be very interested in alternatives

In this case val is unique within foo - there are no duplicates

share|improve this question
    
Do I understand correctly that you want exactly one row in the result of the subquery? –  Erwin Brandstetter Sep 4 '12 at 16:03
    
Which subquery? –  Jack Douglas Sep 4 '12 at 17:40
    
The one you mention in the title. I wasn't sure whether it should be one result after or before "distinct". –  Erwin Brandstetter Sep 4 '12 at 18:00
    
Ah yes, that one :) I was rather confusingly referring to the sub-query in my answer - yours is far more specific and flexible, eg you can also use count(distinct val), though in my real-world case it makes no difference –  Jack Douglas Sep 4 '12 at 21:05
add comment

4 Answers

up vote 6 down vote accepted

Concise, fast (especially with many rows), my favorite concerning readability and would work with dupes, too:

SELECT count(*) = 1 AND min(val) = 1 FROM foo;

Returns TRUE / FALSE .. or NULL - only in the case of exactly one row with val IS NULL, because count() never returns NULL or no row.

The second 1 in the example just happens to be the same as the first, because of your example.


The query in the question fails with NULL values. Consider the simple demo:

CREATE TABLE foo (id int, val int);
INSERT INTO foo VALUES (1, 1),(2, NULL);

SELECT 'yes' 
WHERE      EXISTS(SELECT * FROM foo WHERE val =  1)
AND    NOT EXISTS(SELECT * FROM foo WHERE val <> 1);

IS DISTINCT FROM would fix this, but it could still fail with duplicates in val - which you have ruled out for this case.


Your answer works fine.
Returns 'yes' / no row.

I would prefer this shorter form, though. Don't forget that PostgreSQL (unlike Oracle) has a proper boolean type.

SELECT array_agg(val) = array[1] FROM foo;

Returns TRUE / FALSE / NULL.

share|improve this answer
    
excellent, thanks, I knew there would be a better way :) –  Jack Douglas Sep 4 '12 at 17:38
add comment

A variation on @Erwin's answer. No COUNT() at all, only MIN() and MAX(). It may be slightly more efficient with big table and (not in your case) duplicate val:

SELECT MIN(val) = 1 AND MAX(val) = 1 FROM foo;
share|improve this answer
    
+1 thanks. It handles nulls and duplicates differently of course (if there were any) –  Jack Douglas Sep 5 '12 at 12:07
    
@Jack: Yes. Does your table have nulls? Or you want answers for both cases (with and without)? –  ypercube Sep 5 '12 at 12:12
    
no mine doesn't - I can use either :) –  Jack Douglas Sep 5 '12 at 14:14
    
Would be much faster on bigger tables with a matching index, but performs identically in the absence of such an index - like when testing query results. –  Erwin Brandstetter Sep 5 '12 at 18:47
add comment
select 'yes' where (select array_agg(val) from foo)=array[1];
share|improve this answer
add comment

This one returns true, false or an empty result:

 select j.val is null 
 from foo left join foo as j on j.val <> foo.val 
 where foo.val = 1 limit 1;
share|improve this answer
    
at first glance, this doesn't seem to return false if there are values in foo where val<>1? –  Jack Douglas Sep 5 '12 at 7:42
    
@JackDouglas Oh, sorry. I understood the task wrong the first time. Fixed. –  grayhemp Sep 5 '12 at 8:51
    
Works - except with NULL as value which has not been ruled out in this case. –  Erwin Brandstetter Sep 5 '12 at 12:31
    
@ErwinBrandstetter NULL can be worked out by using IS [NOT] DISTINCT FROM I think. –  grayhemp Sep 8 '12 at 11:05
    
@grayhemp: Not in this case. LEFT JOIN foo j ON j.val <> foo.val fails to detect a row with j.val IS NULL to begin with. If you'd include it with ON j.val IS DISTINCT FROM foo.val you'd then need to check on another column of j defined NOT NULL to tell the two cases apart. But no additional column is defined. –  Erwin Brandstetter Sep 8 '12 at 13:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.