How to dynamically add columns in PostgresSQL select?

Question

I have this table:

+------+----+-------+
| Time | Id | Value |
+------+----+-------+
| 1    | 1  | 0     |
+------+----+-------+
| 2    | 1  | 10    |
+------+----+-------+
| 3    | 1  | 20    |
+------+----+-------+
| 1    | 2  | 0     |
+------+----+-------+
| 2    | 2  | -10   |
+------+----+-------+
| 3    | 2  | -20   |
+------+----+-------+
| 1    | 3  | 0     |
+------+----+-------+
| 2    | 3  | 0     |
+------+----+-------+
| 3    | 3  | 0     |
+------+----+-------+

I want to create an SQL select statement that would return me this table:

+------+--------+--------+--------+
| Time | Value1 | Value2 | Value3 |
+------+--------+--------+--------+
| 1    | 0      | 0      | 0      |
+------+--------+--------+--------+
| 2    | 10     | -10    | 0      |
+------+--------+--------+--------+
| 3    | 20     | -20    | 0      |
+------+--------+--------+--------+

• Q: Why I would not change my structure in the first place?
• A: The ID's are linked to IoT device details and they are added dynamically (meaning that customer will one day have 40 of devices working and other 70).

What I want to achieve? The SQL statement should be working with the ID's dynamically (meaning that the number of returned columns will change based on how many different ID's there are).

What I have tried? I have tried various SQL statements but my knowledge is fairly limited and I can't do it on my own ... Group by time gives me ID and Value column in only aggregate form.

Other info:

• PostgresSQL is running on Supabase platform
• PostgresSQL version is 14
• My front end is Grafana Cloud. I am trying to get the result table into chart. And I can get the data there only through SQL API calls.

Answer 1

Displaying a various list of columns whose number and labels are known only at the runtime is possible but not so obvious with postgres. You need some dynamic sql code.

Here is a full dynamic solution which relies on the creation of a user-defined composite type and on the standard functions jsonb_populate_record and jsonb_object_agg :

First you create the procedure that will create / update the list of ids as a composite type :

CREATE OR REPLACE PROCEDURE id_list () LANGUAGE plpgsql AS
$$
DECLARE id_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident('value' || id :: text) || ' integer', ',')
    INTO id_list
    FROM your_table ;

  EXECUTE 'DROP TYPE IF EXISTS id_list ' ;
  EXECUTE 'CREATE TYPE id_list AS (' || COALESCE(id_list, '') || ')' ;
END ;
$$ ;
CALL id_list () ; -- set up the type id_list with the existing list of ids

Then you can update the type id_list () by trigger when the list of ids is supposed to be modified in the database :

CREATE OR REPLACE FUNCTION your_table_insert_update()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM your_table WHERE id = NEW.id)
  THEN EXECUTE 'ALTER TYPE id_list ADD ATTRIBUTE ' || quote_ident('value' || NEW.id) || ' integer' ;
  END IF ;
  RETURN NEW ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER your_table_insert_update BEFORE INSERT OR UPDATE OF id ON your_table 
FOR EACH ROW EXECUTE FUNCTION your_table_insert_update() ;

CREATE OR REPLACE FUNCTION your_table_delete()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM your_table WHERE id = OLD.id)
  THEN EXECUTE 'ALTER TYPE id_list DROP ATTRIBUTE IF EXISTS ' || quote_ident('value' || OLD.id) || ' integer' ;
  END IF ;
  RETURN OLD ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER your_table_delete AFTER DELETE ON your_table 
FOR EACH ROW EXECUTE FUNCTION your_table_delete() ;

Finally, you should get the expected result with the following query :

SELECT time
     , (jsonb_populate_record(NULL :: id_list, jsonb_object_agg('value' || id :: text, value))).*
  FROM your_table AS t
 GROUP BY time
 ORDER BY time

full test result in dbfiddle.