Turning a multi Column Table into a Key name Value tuple table, in postgresql

Question

I Have a large number of queries some of which can take half an hour to run, many of these queries return over 20 or so columns( yes they are normalised). I am interested in storing all of this data in a single table, in the format for Timestamp, name, value, the reson for this is for later automation on the data.

However when I have a query that returns

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

and I wish to have the data in 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 union on selects for each value v1, v2 and v3,

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

so the question is , is there a way to do this apart from dumping into a temporary table and running subsequent selects onto the relevant columns of the table and unioning them together..

would this be possible with a custom built postgresql function?

thanks in advance

score 2 · Answer 1 · edited May 23 '17 at 12:20

2

This is completely doable with a custom PostgreSQL function. You may also be able to use the tablefunc module and use the crosstab function to do what you want. It looks like you're trying to accomplish an unpivot in PostgreSQL, so you may get more use out of this mailing list thread Unpivot with PostgreSQL

edited May 23 '17 at 12:20

Community

1
1

answered Nov 25 '11 at 14:45

Marc Morin · Accepted Answer · 2012-01-01T16:07:37.880

You can achieve what you want with a variant 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

Turning a multi Column Table into a Key name Value tuple table, in postgresql

2 Answers2