SQL: Join information from two tables

I have three relational postgres tables (timescale hypertables) and need to get my data into a CSV file, but I am struggling to get it to the format I want. I am using django as frameworks, but I need to solve this with raw SQL.

Imagine I have 2 tables: drinks and consumption_data.

The drinks table looks like this:

 name    | fieldx | fieldy 
---------+--------+------
 test-0  |        | 
 test-1  |        | 
 test-2  |        |

The consumption_data table looks like this:

          time          | drink_id    |  consumption 
------------------------+-------------+------------------------------
 2018-12-15 00:00:00+00 |           2 |                          123
 2018-12-15 00:01:00+00 |           2 |                          122
 2018-12-15 00:02:00+00 |           2 |                          125

My target table should join these two tables and give me all consumption data with the drink names back.

          time          | test-0      |  test-1 | test-2
------------------------+-------------+---------+-------
 2018-12-15 00:00:00+00 |         123 |  123    |  22
 2018-12-15 00:01:00+00 |         334 |  122    |  32
 2018-12-15 00:02:00+00 |         204 |  125    |  24

I do have all the drink-ids and all the names, but those are hundreds or thousands.

I tried this by first querying the consumption data for a single drink and renaming the column: SELECT time, drink_id, "consumption" AS test-0 FROM heatflowweb_timeseriestestperformance WHERE drink_id = 1;

Result:

          time          | test-0      | 
------------------------+-------------+
 2018-12-15 00:00:00+00 |         123 | 
 2018-12-15 00:01:00+00 |         334 |  
 2018-12-15 00:02:00+00 |         204 |

But now, I would have to add hundreds of columns and I am not sure how to do this. With UNION? But I don't want to write thousands of union statements...

Maybe there is an easier way to achieve what I want? I am not an SQL expert, so what I need could be super easy to achieve or also impossible... Thanks in advance for any help, really appreciated.

Back to Top