cohdez cohdez - 2 months ago 11
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:

object_name
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
attr_name
, 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:

object_name
attr_1
attr_2
etc.


Each attribute
attr_x
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

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

SELECT
    object_name,
    (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:

CREATE VIEW myview AS SELECT ...
                      ^^^ 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.

Comments