title: Querying a metrics schema on PostgreSQL
body: <p>I have the following schema representing a simple metrics storage:</p>
  
  <pre><code>CREATE TABLE targets (
      target varchar
  );
  
  CREATE TABLE reads (
      at timestamp without time zone,
      target varchar
  );
  
  CREATE TABLE updates (
      at timestamp without time zone,
      target varchar
  );
  </code></pre>
  
  <p>The relations <code>reads</code> and <code>updates</code> store the occurrence of an event on a specific target at a given time.</p>
  
  <p>These are same sample data:</p>
  
  <pre><code>COPY targets (target) FROM stdin;
  A
  B
  C
  \.
  
  COPY reads (at, target) FROM stdin;
  1970-01-01 03:40:00 A
  1970-01-01 06:00:00 B
  1970-01-01 05:00:00 A
  1970-01-03 05:00:00 A
  1970-01-04 01:00:00 B
  \.
  
  COPY updates (at, target) FROM stdin;
  1970-01-01 01:00:00 A
  1970-01-01 01:00:00 B
  1970-01-01 02:00:00 A
  1970-01-01 04:00:00 A
  1970-01-02 01:00:00 A
  1970-01-02 01:00:00 B
  1970-01-04 01:00:00 B
  \.
  </code></pre>
  
  <p>I would get a report with all metrics counting the occurrences by date for each target, similar to the following query (eventually also without "zero" rows) but in a more efficient way:</p>
  
  <pre><code>select t.target, day::date,
      coalesce((select count(*) from updates where target = t.target and at::date = day), 0) updates,
      coalesce((select count(*) from reads   where target = t.target and at::date = day), 0) reads
  from 
      generate_series('1970-01-01'::date, '1970-01-04'::date, '1 day'::interval) day,
      targets t
  order by target, day;
  
   target |    day     | updates | reads 
  --------+------------+---------+-------
   A      | 1970-01-01 |       3 |     2
   A      | 1970-01-02 |       1 |     0
   A      | 1970-01-03 |       0 |     1
   A      | 1970-01-04 |       0 |     0
   B      | 1970-01-01 |       1 |     1
   B      | 1970-01-02 |       1 |     0
   B      | 1970-01-03 |       0 |     0
   B      | 1970-01-04 |       1 |     1
   C      | 1970-01-01 |       0 |     0
   C      | 1970-01-02 |       0 |     0
   C      | 1970-01-03 |       0 |     0
   C      | 1970-01-04 |       0 |     0
  </code></pre>
  
  <p>Any suggestions?</p>