Before the Shadow Play

Tag: aggregate functions

  • Postgres array_to_string() and array_agg() to decouple your interface

    Let’s say you’ve got a collection of job data for a set of users that looks like this, and you want to create a nice summary of it to be displayed, with a count of how many jobs are in each category for each user, all on one line.

     │id │ user_id │ job │ status    │
 ├───┼─────────┼─────┼───────────┤
 │ 1 │ 12      │ 1   │ Completed │
 │ 2 │ 12      │ 2   │ Cancelled │
 │ 3 │ 14      │ 3   │ Ready     │
 │ 5 │ 14      │ 4   │ Completed │
 │ 6 │ 14      │ 4   │ Completed │
 │ 7 │ 14      │ 4   │ Cancelled │
 ...

    Here’s  the report of summarized statuses of jobs for each user that you want.

    │ user_id │ summary                           │
├─────────┼───────────────────────────────────┤
│ 12      | 1 Cancelled, 1 Completed          │
│ 14      | 1 Cancelled, 2 Completed, 1 Ready │

    I’ll show you how it’s possible to provide this solely with a Postgres SELECT.

    (more…)