I have this table definition for sensor events:

                      Table "public.sen_sensor_event"
   Column    |            Type             | Collation | Nullable | Default 
 dtype       | character varying(31)       |           | not null | 
 id          | bigint                      |           | not null | 
 created     | timestamp without time zone |           |          | 
 type        | character varying(255)      |           | not null | 
 temperature | double precision            |           |          | 
 sensor_id   | bigint                      |           | not null | 
 updated     | timestamp without time zone |           |          | 

I'm looking to get a summary for each sensor for each event type. Currently I'm doing it via this materialized view which gives the created timestamp of most recent event for each type (mapped to an enum in code) and a total event count.

CREATE MATERIALIZED VIEW public.sen_sensor_summary AS
 SELECT x.belongs_to,
   FROM (public.sen_sensor x
     FULL JOIN ( SELECT COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id) AS sensor_id,
            a.created AS temperature_created,
            b.created AS human_motion_created,
            c.created AS presence_created,
            d.created AS heartbeat_created,
            e.created AS startup_created
           FROM (((((( SELECT DISTINCT ON (z_1.sensor_id) z_1.sensor_id,
                    count(z_1.sensor_id) AS event_count
                   FROM public.sen_sensor_event z_1
                  GROUP BY z_1.sensor_id
                  ORDER BY z_1.sensor_id) z
             FULL JOIN ( SELECT DISTINCT ON (a_1.sensor_id) a_1.sensor_id,
                   FROM public.sen_sensor_event a_1
                  WHERE ((a_1.type)::text = 'TEMPERATURE'::text)
                  ORDER BY a_1.sensor_id, a_1.created DESC) a ON ((z.sensor_id = a.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (b_1.sensor_id) b_1.sensor_id,
                   FROM public.sen_sensor_event b_1
                  WHERE ((b_1.type)::text = 'HUMAN_MOTION'::text)
                  ORDER BY b_1.sensor_id, b_1.created DESC) b ON ((z.sensor_id = b.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (c_1.sensor_id) c_1.sensor_id,
                   FROM public.sen_sensor_event c_1
                  WHERE ((c_1.type)::text = 'PRESENCE'::text)
                  ORDER BY c_1.sensor_id, c_1.created DESC) c ON ((z.sensor_id = c.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (d_1.sensor_id) d_1.sensor_id,
                   FROM public.sen_sensor_event d_1
                  WHERE ((d_1.type)::text = 'HEARTBEAT'::text)
                  ORDER BY d_1.sensor_id, d_1.created DESC) d ON ((z.sensor_id = d.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (e_1.sensor_id) e_1.sensor_id,
                   FROM public.sen_sensor_event e_1
                  WHERE ((e_1.type)::text = 'STARTUP'::text)
                  ORDER BY e_1.sensor_id, e_1.created DESC) e ON ((z.sensor_id = e.sensor_id)))
          ORDER BY COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id)) y ON ((x.id = y.sensor_id)))
  ORDER BY x.name

I like the resulting view, which I map to a SensorSummary object in Java via Hibernate. However, for each new event type I add I need to add a new FULL JOIN section to the view definition.

Is there a more dynamic way to define the materialized view so that when a new type value appears it is automatically added to the view?

  • 1
    Unrelated, but: you have way too many nested parentheses. This could also be written as from sen_sensor full join ( ... ) z on .. full join (...) a on .... full join (...) b on .. – a_horse_with_no_name Jul 28 '20 at 6:16

There is no way where you don't have to touch your query when new types are introduced, because the number and type of the columns of a query must be known to the database before the query is actually run (they are evaluated at parse time).

But you can simplify your query by using filtered aggregation to get the different created columns in the output. You also don't need a full join as you make sen_sensor the driving table. A left join is enough in this case (assuming you never have sensors in the even table that don't exist in the sensor table).

SELECT x.belongs_to,
       y.temperature[1] as temperature,
FROM sen_sensor x
  left join (
      select sensor_id, 
             count(*) as event_count,
             array_agg(temperature order by created desc) filter (where type = 'TEMPERATURE') temperature,
             max(created) filter (where type = 'TEMPERATURE') as temperature_created,
             max(created) filter (where type = 'HUMAN_MOTION') as human_motion_created,
             max(created) filter (where type = 'PRESENCE') as presence_created,
             max(created) filter (where type = 'HEARTBEAT') as heartbeat_created,
             max(created) filter (where type = 'STARTUP') as startup_created
      from sen_sensor_event
      group by sensor_id
  ) as y on y.sensor_id = x.sensor_id      

The array_agg(temperature ...) is necessary to get the "latest temperature" as Postgres has no "last_value" as an ordered-set aggregate function (only as a window function). Aggregating the values into a sorted array and then picking the first one serves essentially the same purpose.

When a new type is added, you only add a new max(...) filter (...) expression, which still needs a manual change, but is a lot less to type. In fact, you could have a function (or procedure) that generates the necessary SQL dynamically based on the available types.

This should also be substantially faster than your solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.