Today I Learned

A Hashrocket project

Generating a postgres query with no rows

For testing purposes, it is nice to be able to simulate a query that does not result in any rows.

select * from generate_series(0, -1) x;
 x
---
(0 rows)

This is because (from the docs):

When step is positive, zero rows are returned if start is greater than stop.

Step (the third argument to generate series) defaults to 1, so anything where the from has to go backwards to get to the to will result in 0 rows.

select * from generate_series(4, 3) x;
 x
---
(0 rows)

And now you can see how sum() behaves without any rows:

select sum(x) from generate_series(1, 0) x;
sum
-----
   ø
(1 row)

Ohhh, it returns null. I hope you’re planning for that.

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, a series of free weekly PostgreSQL screencasts.