crosstab in PostgreSQL

david
Rate this post

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, valeur 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 the manually set the columns’ name.

Related posts