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<>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 <> 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.

StackExchange Database Administrators Q#23637, answer score: 8