cohdez cohdez - 1 year ago 69
SQL Question

Create a wide view of a database table in PostgreSQL

I have a PostgreSQL table which stores attributes about different physical objects. It has the following fields:

attr_name (string e.g. attr_1, attr_2 etc.)
attr_value (number)
date_modified (date)
priority (integer)

There may be many different values for the same
, which will be added at different dates and with different priority level (representing the accuracy of the source), so I need this structure for traceability later.

I would like to create a view of this table so that I can query it in an efficient way for multiple (~100) attributes e.g.

SELECT * FROM View1 WHERE attr_1 = 5 and attr_2 > 3 etc.

So in order to do this, I think I need a view with fields like:


Each attribute
should contain the highest priority version of the attribute from the original table. Does anyone know how to do this? I only know very basic SQL so I don't even know what this is called or if it is possible. Has anyone done this before, in any SQL-type database? I'd like to avoid having 2 separate tables if possible. Happy to accept any other solution which accomplishes both the traceability and the fast querying requirements.

Answer Source

Here's an example query that returns the all the values with the highest priority in the format that you want:

    (SELECT attr_value AS attr_1
     FROM t
     WHERE t.object_name=t1.object_name AND attr_name='attr_1'
     ORDER BY priority DESC LIMIT 1),
    (SELECT attr_value AS attr_2
     FROM t
     WHERE t.object_name=t1.object_name AND attr_name='attr_2'
     ORDER BY priority DESC LIMIT 1)
FROM (SELECT DISTINCT object_name FROM t) t1;

I assumed that the table is called t. The query returns 2 attr_??? columns. If you have more, you have to add more subqueries, one for each column. If you don't like that (e.g. because you have a large number of attr_name), then we can do something that does not require to write a huge query but it will return the results as JSON objects (so it will be in different format).

If you want to create a view, just run the following:

                      ^^^ insert the query from above

If you need this to be as fast as possible, I would recommend using 2 tables: one for the highest priority values and one for everything else.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download