crosstab in PostgreSQL

I have the following tables:

CREATE TABLE sensor (
    id int PRIMARY KEY,
    abbrv varchar(255) NOT NULL UNIQUE
);

and

CREATE TABLE readings (
    time timestamp without time zone NOT NULL,
    device_id int NOT NULL,
    sensor_id int REFERENCES sensor (id) NOT NULL,
    value float4 NOT NULL
);

How can I query the database so that it returns the table

time | device_id | abbrv $1 | ... | abbrv $n

where n ranges over the rows of the table sensor and ‘abbrv $i’ is replaced by the corresponding value in the table sensor?

The following query

SELECT * FROM crosstab(
    'SELECT time, device_id, sensor_id, value FROM readings ORDER BY 1, 2',
    'SELECT id FROM sensor'
    ) AS (time timestamp without time zone, device_id int, "sensor_1" float4, "sensor_2" float4, "sensor_3" float4);

works up to a certain extent: I need to know how many rows there are in the sensor table and I have to manually set the columns’ name.

Answer

I don’t have a basic answer better than yours, especially when the number of sensors/values vary per device. However, you can do something like the following :

First you can create a composite TYPE which corresponds to the list of columns with the total list of sensor.abbrv from the table sensor ordered by sensor.id :

CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(quote_ident(abbrv) || ' float4', ',' ORDER BY id ASC)
    INTO column_list
    FROM sensors ;
  
  EXECUTE 'DROP TYPE IF EXISTS composite_type ;'
  EXECUTE '
  CREATE OR REPLACE TYPE composite_type (time timestamp without time zone, device_id int, ' || column_list || ')' ;
END ;
$$ ;

CALL create_composite_type() ;

Then you can aggregate the varying number of sensor.abbrv / reading.value pairs as json key/value pairs within a single json object per time and device_id :

SELECT time
     , device_id
     , jsonb_object_agg(jsonb_build_object(quote_ident(s.abbrv), quote_nullable(r.value)) ORDER BY s.id ASC) AS json_columns
  FROM readings AS r
 RIGHT JOIN sensor AS s
    ON s.id = r.sensor_id
 GROUP BY time, device_id

RIGHT JOIN is required so that to systematically create a json key/value pair even if the json key s.abbrv doesn’t correspond to any value in the readings table.

Finally you can display the json object as a row using the jsonb_populate_record function :

SELECT time
     , device_id
     , jsonb_populate_record(NULL :: composite_type, json_columns)
  FROM 
     ( SELECT time
            , device_id
            , jsonb_object_agg(jsonb_build_object(quote_ident(s.abbrv), quote_nullable(r.value)) ORDER BY s.id ASC) AS json_columns
         FROM readings AS r
        RIGHT JOIN sensor AS s
           ON s.id = r.sensor_id
        GROUP BY time, device_id
     ) AS q