“Postgres for Developers” – Notes from PGConf NYC 2014

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).

Example 1: Array Aggregation

“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)

select y, array_agg(x) from (
  select 1 x, 2 y
  union all
  select 2 x, 2 y
  union all 
  select 3 x, 3 y
) a
group by y

2;"{1,2}"
3;"{3}"

If 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:

with t(a, b) as
(
  select y, array_agg(x) from (
    select 1 x, 2 y
    union all
    select 2 x, 2 y
    union all 
    select 3 x, 3 y
  ) a
  group by y
)
select * 
from t t1 join t t2 on t1.b[2] = t2.a

2;"{1,2}";2;"{1,2}"

Example 2: Named Window Functions

I’m not sure yet whether this is just syntactic sugar or has real value, but you can set up named “windows.”

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:

select a, max(b) over (partition by a) 
from (
  select 1 a, 1 b
  union all 
  select 2 a, 1 b
  union all 
  select 1 a, 2 b
) t1

1;2
1;2
2;1

These allow you do calculate aggregate functions (like min/max) without combining all the rows.

For instance, if you sort these values, you can find the “next” or “previous” row in the partition, which is pretty standard sql stuff:

select a, lag(b) over (partition by a order by b) 
from (
  select 1 a, 1 b
  union all 
  select 2 a, 1 b
  union all 
  select 1 a, 2 b
) t1

1;
1;1
2;

If 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:

with t(a, b) as
(
  select y, array_agg(x) from (
    select 1 x, 2 y
    union all
    select 2 x, 2 y
    union all 
    select 3 x, 3 y
  ) a
  group by y
)
select * 
from t t1 join t t2 on t1.b[2] = t2.a

2;"{1,2}";2;"{1,2}"

What’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:

select a, lag(b) over w
from (
  select 1 a, 1 b
  union all 
  select 2 a, 1 b
  union all 
  select 1 a, 2 b
) t1
window w as (partition by a order by b) 

1;
1;1
2;

Example 3: Ranges
Postgres 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:

with _data as (
  select 1 a, 1 b
  union all 
  select 2 a, 1 b
  union all 
  select 1 a, 2 b
  union all
  select 2 a, 2 b
),
_history as (
  select a, array_agg(b) _start, array_agg(b) _end
  from _data
  group by a
)
select a, 
       _start[1], 
       _end[1], 
       int4range(_start[1]::integer, _end[2]::integer, '(]'::text) 
from _history

1;1;1;"[2,3)"
2;1;1;"[2,3)"

There 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.

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.

If you’re in this area of functionality, also check out btree_gist indexes, which may be helpful for tuning this.

Example 4: DISTINCT ON
Postgres has a feature to pull back the first value for a row in a group by. I assume this is a performance feature, but at the least it’s a very concise syntax for something that would otherwise require the use of RANK(). I imagine that you’d always want to use an ORDER BY with it.

The example from the docs for this one is pretty clear:

SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;

There are a few other features that got a lot of play at the conference (e.g. foreign data wrappers) – more to come.