Complete Series From Partial Series I'm struggling to come up with an answer to this problem and so hope to find some help here.
I have the following time series data table with missing entries:

CREATE TABLE foo
AS
  SELECT timestamp::timestamp, deviceid, eventcount
  FROM ( VALUES
    ( '2017-01-01 00:00:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:30:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:40:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:00:00', 'BBBBB', 2000 ),
    ( '2017-01-01 00:10:00', 'BBBBB', 2000 ),
    ( '2017-01-01 00:50:00', 'BBBBB', 2000 )
  ) AS t(Timestamp, DeviceID, EventCount);


I need to generate from this a complete series i.e. 

+---------------------+----------+------------+
|      Timestamp      | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | AAAAA    |       1000 |
| 2017-01-01 00:10:00 | AAAAA    |          0 |
| 2017-01-01 00:20:00 | AAAAA    |          0 |
| 2017-01-01 00:30:00 | AAAAA    |       1000 |
| 2017-01-01 00:40:00 | AAAAA    |       1000 |
| 2017-01-01 00:50:00 | AAAAA    |          0 |
| 2017-01-01 00:00:00 | BBBBB    |       2000 |
| 2017-01-01 00:10:00 | BBBBB    |       2000 |
| 2017-01-01 00:20:00 | BBBBB    |          0 |
| 2017-01-01 00:30:00 | BBBBB    |          0 |
| 2017-01-01 00:40:00 | BBBBB    |          0 |
| 2017-01-01 00:50:00 | BBBBB    |       2000 |
+---------------------+----------+------------+


I have tried generating a series of timestamps with an interval of 10 minutes and then left joining on the first table but what I end up with is this: 

+---------------------+----------+------------+
|      Timestamp      | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | NULL     |          0 |
| 2017-01-01 00:10:00 | NULL     |          0 |
| 2017-01-01 00:20:00 | NULL     |          0 |
| 2017-01-01 00:30:00 | NULL     |          0 |
| 2017-01-01 00:40:00 | NULL     |          0 |
| 2017-01-01 00:50:00 | NULL     |          0 |
| 2017-01-01 00:00:00 | AAAAA    |       1000 |
| 2017-01-01 00:30:00 | AAAAA    |       1000 |
| 2017-01-01 00:40:00 | AAAAA    |       1000 |
| 2017-01-01 00:00:00 | BBBBB    |       2000 |
| 2017-01-01 00:10:00 | BBBBB    |       2000 |
| 2017-01-01 00:50:00 | BBBBB    |       2000 |
+---------------------+----------+------------+
This should work for you.

SELECT t.timestamp, t.deviceid, coalesce(eventcount, 0)
FROM foo
RIGHT OUTER JOIN (
  SELECT timestamp, deviceid
  FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
  CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
  CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)
) AS t
ON foo.deviceid = t.deviceid
AND foo.timestamp = t.timestamp
ORDER BY 2, 1;


In the first run we generate a table with this

SELECT timestamp, deviceid
FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)


This table we generate looks like this

      timestamp      | deviceid 
---------------------+----------
 2017-01-01 00:00:00 | AAAAA
 2017-01-01 00:10:00 | AAAAA
 2017-01-01 00:20:00 | AAAAA
 2017-01-01 00:30:00 | AAAAA
 2017-01-01 00:40:00 | AAAAA
 2017-01-01 00:50:00 | AAAAA
 2017-01-01 00:00:00 | BBBBB
 2017-01-01 00:10:00 | BBBBB
 2017-01-01 00:20:00 | BBBBB
 2017-01-01 00:30:00 | BBBBB
 2017-01-01 00:40:00 | BBBBB
 2017-01-01 00:50:00 | BBBBB
(12 rows)


It has all the time-ranges needed. Then we RIGHT-OUTER join it.

The end result looks like this

      timestamp      | deviceid | coalesce 
---------------------+----------+----------
 2017-01-01 00:00:00 | AAAAA    |     1000
 2017-01-01 00:10:00 | AAAAA    |        0
 2017-01-01 00:20:00 | AAAAA    |        0
 2017-01-01 00:30:00 | AAAAA    |     1000
 2017-01-01 00:40:00 | AAAAA    |     1000
 2017-01-01 00:50:00 | AAAAA    |        0
 2017-01-01 00:00:00 | BBBBB    |     2000
 2017-01-01 00:10:00 | BBBBB    |     2000
 2017-01-01 00:20:00 | BBBBB    |        0
 2017-01-01 00:30:00 | BBBBB    |        0
 2017-01-01 00:40:00 | BBBBB    |        0
 2017-01-01 00:50:00 | BBBBB    |     2000
(12 rows)