Blogmark

the tl;dr of this article is that you can avoid a bunch of ORM/query building and extraneous app logic by leaning on the expressiveness and capability of SQL.

The query building example in this post is a good illustration of why where 1 = 1 shows up in some SQL queries, usually in the logs from an ORM.

Interesting: one example uses Postgres' cardinality(some_array) = 0 to check if an array is empty or not. For one-dimensional array, cardinality is a bit more straightforward than array_length and requires only one argument.

Of further note, cardinality determines the number of items in an array regardless of how many dimensions an array is.

> select cardinality(array[1,2]);
+-------------+
| cardinality |
|-------------|
| 2           |
+-------------+

> select cardinality(array[[1,2], [3,4]]);
+-------------+
| cardinality |
|-------------|
| 4           |
+-------------+

> select cardinality(array[[[1,2,3], [4,5,6], [7,8,9]]]);
+-------------+
| cardinality |
|-------------|
| 9           |
+-------------+