How to check if a subquery has exactly one distinct result and a specified value concisely? I found myself writing the following:

select 'yes' 
where exists(select * from foo where val=1)
and not exists(select * from foo where val&lt;&gt;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
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 &lt;&gt; 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.