How to check if a subquery has exactly one distinct result and a specified value concisely?

Question

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

Answer 1

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.

Answer 2

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;

Answer 3

select 'yes' where (select array_agg(val) from foo)=array[1];

Answer 4

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;