Unpivot using postgresql

Question

i tried to unpivot a table with many columns (unknown number) into a table with four columns.

My query looks like this:

INSERT INTO measurement (timestamp, sensor, value)
SELECT d."Timestamp", 
       ( SELECT s.id FROM sensor s WHERE s.name = columns.column_name),
       ( SELECT columns.column_name from sensor_list where "Timestamp" = d."Timestamp")     
FROM   sensor_list d, 
       (SELECT column_name FROM information_schema.columns WHERE table_name = 'sensor_list' AND column_name <> 'Timestamp' ORDER BY ordinal_position) columns

This doesnt work. The Errormessage i receive is:

"ERROR: >>value<< has type numeric, but the expression is of type  information_schema.sql_identifier"

Is there any chance to get it work by converting "columns.column_name" to string or something else like with quote_ident() ?

All i tried did not work. Thank you very much.

Madhivanan · Accepted Answer · 2013-01-23 10:22:34Z

1

If the datatype of the column Value is numeric, the code will give error. Do you want to assign column name or column

answered Jan 23, 2013 at 10:22

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Einstein Over a year ago

I think i want to use column_name. As sensor_list looks like: Timestamp | Sensor1 | Sensor2 | Sensor3 | ... | Sensor 999 | ------------------------------------------------------------- time | value | value | ...

Collectives™ on Stack Overflow

Unpivot using postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related