how to add a query to postgres and have it show up as a table

All we need is an easy explanation of the problem, so here it is.

I’m not sure what technology I need because I’m not a DBA, but I’m sure there’s a good way to do this.

I have a postgres database with this schema (top two tables exist):

how to add a query to postgres and have it show up as a table

So I’m trying to add the 3rd table to the database. It’s a subset of observation: all latest observations by stream_id and target_id with the target_key value already joined into it.

I know upfront that 99% of the queries against the target table are asking for the latest row of a certain stream_id and target_id. so I thought instead of manually building another table with duplicate data, I’ll try to leverage the power of the database to make a temporal table, or a foreign data wrapper or something, that is essentially a hard coded query on the database which looks like a table we can query, (and since it’s querying a subset of the data, its much faster).

Ok, so that’s what I’m looking for but I don’t know which technology to use, as I mentioned I’ve been searching and I found temporal tables, virtual tables and FDW. But I’m a programmer not a DBA so I’m having a hard time telling the difference or understanding which one matches my need.

What technology can I use for this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I discovered I’m looking for a view. And I think this’ll basically work:

CREATE VIEW latest_observation AS
  SELECT distinct ON (
    SELECT ob.stream_id, ob.target_id
      FROM obseration ob
      GROUP  BY ob.stream_id, ob.target_id
      HAVING count(*) = 1)
    o.id AS observation_id,
    o.stream_id,
    s.source_name AS source,
    s.stream_name AS stream,
    t.target_name AS target,
    o.inserted_at,
    o.value
  FROM observation o
     INNER JOIN target t USING (target_id);
     INNER JOIN stream s USING (stream_id);
  ORDER BY o.inserted_at DESC;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply