Here is a simplified version of the problem I'm trying to solve:

Table A with id and some data:

id,  data
A01, xxxx
A02, xxxx

Table B with id and some other kind of data

id,  data
B01, xxxx
B02, xxxx

Table Events with data that looks like this:

timestamp, event_type,  object_id
xxxxxxxxx, A_something, A01
xxxxxxxxx, B_thing,     B01
xxxxxxxxx, A_this,      A02
xxxxxxxxx, A_that,      A03

I have a query that retrieves events, using a timestamp range, and then I need to get the data from the different tables into arrays.


xxxxxxxxx, A_something, A01
xxxxxxxxx, B_thing,     B01
xxxxxxxxx, A_this,      A02

would need to fetch:

  • A01 from table A
  • B01 from table B
  • A02 from table A

and then make an array of arrays out of it:

    [ columns from A01 ],
    [ columns from B01 ],
    [ columns from A02 ]

The output can also be a json string.

And I have absolutely no clue how this can be done.

Any pointers would be greatly appreciated.

  • The final example makes me believe you just don't want to get data from A or B, but there may be multiple columns in those tables? While it is possible to join those tables, I'd probably read all events into my application, collect all possible uses of tables A and B and run them as separate selects against those tables, mapping the data in the application.
    – noctarius
    Feb 21 at 7:02
  • I’m trying to make a materialized view with that data because it’ll be read by many users and I want to avoid a bunch of queries but rather have a single one that aggregates the data over time intervals
    – Thomas
    Feb 21 at 12:59
  • I would still recommend keeping the metadata separately and merging the metadata with the pre-aggregated data in the continuous aggregate at query time (you may want to hide the query behind a normal view which defines the JOIN). Actually for two reasons. First, CAGGs don't support JOINs, secondly because it would basically duplicate the metadata since CAGGs are materialized).
    – noctarius
    Feb 22 at 13:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.