Includes a table with multiple columns in the key name. Tuple value table in postgresql

Question

I have a large number of queries, some of which can take half an hour, many of these queries return more than 20 or so columns (yes, they are normalized). I am interested in storing all this data in one table, in a format for time stamping, name, value, resonance is used for this for subsequent data automation.

However, when I have a request that returns

T1, V1t1, V2t1, V3t1 T2, V1t2, v2t2, v3t2 -- -- -- -- 

and I want to have data in the format

 T1, Name(V1), V1t1 T1, Name(V2), V2t1 T1, Name(V3), V3t1 T2, Name(V1), V1t2 T2, Name(V2), V2t2 T2, Name(V3), V3t2 

I know, I could do this by doing a select join for each value of v1, v2 and v3,

however, I am interested in speed and would like to refrain from issuing 3 requests, where I can receive data (albeit in wroung format) in one .. or in the real case, refrain from issuing 20-30 requests, where it can do in one.

so the question is whether there is a way to do this other than dumping into a temporary table and then selecting into the appropriate columns of the table and joining them together.

is this possible with the custom postgresql function?

early

Answer 1

You can do what you want with the option of the following query.

 CREATE TABLE data ( sampleTime timestamptz, value1 numeric, value2 numeric, value3 numeric ); INSERT INTO data(sampleTime,value1,value2,value3) values(now(),1,2,3); INSERT INTO data(sampleTime,value1,value2,value3) values(now(),4,5,6); SELECT data.sampleTime, CASE WHEN generate_series = 1 THEN 'value1' WHEN generate_series = 2 THEN 'value2' ELSE 'value3' END AS key, CASE WHEN generate_series = 1 THEN value1 WHEN generate_series = 2 then value2 ELSE value3 END AS value FROM data,generate_series(1,3) ORDER BY data.sampleTime,generate_series 

Answer 2

This is fully doable using the special PostgreSQL function. You can also use the tablefunc module and use the crosstab function to do what you want. It looks like you are trying to roll out in PostgreSQL, so you can get more benefit from this Unpivot mailing list with PostgreSQL