I saw a talk by one of the core Postgres developers, which showed a bunch of interesting tricks to handle business rules in Postgres specific SQL. These are all things you could find by reading the documentation, but they are interesting enough to write up examples to highlight some interesting things you can do. A lot of these end up being useful for writing systems with immutable data (especially auditing, and sometimes reporting systems).<\/p>\n
Example 1: Array Aggregation<\/b> <\/p>\n
“array_agg” can be used to combine rows, which sort of resembles a pivot table operation (this is the same set of values that would be passed as arguments to other aggregation functions)<\/p>\n
\nselect y, array_agg(x) from (\n select 1 x, 2 y\n union all\n select 2 x, 2 y\n union all \n select 3 x, 3 y\n) a\ngroup by y\n\n2;\"{1,2}\"\n3;\"{3}\"\n<\/pre>\nIf you use the above table as a common table expression, you can also rename the columns in the with block. You can even join on the arrays:<\/p>\n
\nwith t(a, b) as\n(\n select y, array_agg(x) from (\n select 1 x, 2 y\n union all\n select 2 x, 2 y\n union all \n select 3 x, 3 y\n ) a\n group by y\n)\nselect * \nfrom t t1 join t t2 on t1.b[2] = t2.a\n\n2;\"{1,2}\";2;\"{1,2}\"\n<\/pre>\nExample 2: Named Window Functions<\/b> <\/p>\n
I’m not sure yet whether this is just syntactic sugar or has real value, but you can set up named “windows.”<\/p>\n
By way of explanation, a lot of times when you start using aggregate functions (min, max, array_agg, etc), you end up using window functions, which resemble the following:<\/p>\n
\nselect a, max(b) over (partition by a) \nfrom (\n select 1 a, 1 b\n union all \n select 2 a, 1 b\n union all \n select 1 a, 2 b\n) t1\n\n1;2\n1;2\n2;1\n<\/pre>\nThese allow you do calculate aggregate functions (like min\/max) without combining all the rows.<\/p>\n
For instance, if you sort these values, you can find the “next” or “previous” row in the partition, which is pretty standard sql stuff:<\/p>\n
\nselect a, lag(b) over (partition by a order by b) \nfrom (\n select 1 a, 1 b\n union all \n select 2 a, 1 b\n union all \n select 1 a, 2 b\n) t1\n\n1;\n1;1\n2;\n<\/pre>\nIf you use the above table as a common table expression, you can then rename the columns in the WITH block. You can even join on the arrays:<\/p>\n
\nwith t(a, b) as\n(\n select y, array_agg(x) from (\n select 1 x, 2 y\n union all\n select 2 x, 2 y\n union all \n select 3 x, 3 y\n ) a\n group by y\n)\nselect * \nfrom t t1 join t t2 on t1.b[2] = t2.a\n\n2;\"{1,2}\";2;\"{1,2}\"\n<\/pre>\nWhat’s cool is you can move the “over partition by” part out of the query to the end as a named window, which presumably would be really nice if you had a lot of them, or wanted to re-use the same window for multiple fields:<\/p>\n
\nselect a, lag(b) over w\nfrom (\n select 1 a, 1 b\n union all \n select 2 a, 1 b\n union all \n select 1 a, 2 b\n) t1\nwindow w as (partition by a order by b) \n\n1;\n1;1\n2;\n<\/pre>\nExample 3: Ranges<\/b>
\nPostgres has a really cool feature, as of 9.2, where you can query whether something is in a range (ranges are a special type, kind of like the arrays above). This example is a bit contrived, to show that you could combine array_agg and range creation:<\/p>\n\nwith _data as (\n select 1 a, 1 b\n union all \n select 2 a, 1 b\n union all \n select 1 a, 2 b\n union all\n select 2 a, 2 b\n),\n_history as (\n select a, array_agg(b) _start, array_agg(b) _end\n from _data\n group by a\n)\nselect a, \n _start[1], \n _end[1], \n int4range(_start[1]::integer, _end[2]::integer, '(]'::text) \nfrom _history\n\n1;1;1;\"[2,3)\"\n2;1;1;\"[2,3)\"\n<\/pre>\nThere are a bunch of range types built in (based on numerics, timestamps). Note that you can specify whether the endpoints on ranges are inclusive or exclusive.<\/p>\n
You can find out if a data value is within a range with the @> operator and see if two ranges overlap with &&. This set of functionality is great for exploring audit records – if you make a range with “[valid_from, valid_to)” you can query to find out what rows were effective on a particular date\/time, for instance.<\/p>\n