Today I Learned

hashrocket A Hashrocket project

Treat a subquery like a value

Postgres is cool with returning a value from a query.

select 1;

And also returning a value from a subquery.

select (select 1);

Turtles all the way down.

select (select (select 1));

But that subquery can only return one row or you'll get an error.

select (select unnest('{1, 2}'::integer[]));
ERROR:  more than one row returned by a subquery used as an expression

This is used sometimes when you need to use a count in an expression.

select round((select count(*) from pg_class where reltype = 0) / (select count(*)::float from pg_class) * 100) as percent_with_zero_reltype;
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.