# Archiving multiple values to single table in psql database using ADO output adapter **URL:** \ **Category:** openPDC\ **Created:** [October 30, 2023, 7:13am UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212 "2023-10-30T07:13:14Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![BALAJI\_TS](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/balaji_ts/32/1277_2.png) [@BALAJI\_TS](http://discussions.gridprotectionalliance.org/u/BALAJI_TS)\ **Post date:** [October 30, 2023, 7:13am UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/1 "2023-10-30T07:13:14Z")
Hello, We have created a ADO output adapter for PSQL database in openPDC to archive PMU readings. And we are able send the data to PSQL table. But we are able to send only value to PSQL table using the below output adapter. BulkInsertLimit=500; DataProviderString={AssemblyName={Npgsql, Version=4.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7}; ConnectionType=Npgsql.NpgsqlConnection; AdapterType=Npgsql.NpgsqlDataAdapter}; DbConnectionString={Server=localhost; Port=5432; Database=postgres; User Id=postgres; Password=abc1234}; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value; SourceIDs=PPA; InputMeasurementKeys=c496047d-c86d-4927-b935-a3c18d4af168 But our requirement is to send multiple values(like freq, phase, amplitude, etc) to a single table in PSQL database. How can we do that? From the below topic we found that we cann’t send multiple values to single table using one AD adapter. So they suggested to use JOIN feature in SQL. > [@Configuration to get data from database](https://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/32): > > You can’t do it, because that would require concentration which is not supported by the ADO adapter. The expectation is that you’d handle concentration after you have archived your data. Here’s a simple example, based on various assumptions, that uses SQL JOINs to concentrate the TimeSeriesMeasurement table. WITH tsm AS ( SELECT SignalType.Acronym AS SignalType, TimeSeriesMeasurement.Timestamp, TimeSeriesMeasurement.Value FROM TimeSeriesMeasurement JOIN … And in that they have provided one simple example about using JOIN like below. WITH tsm AS ( SELECT SignalType.Acronym AS SignalType, TimeSeriesMeasurement.Timestamp, TimeSeriesMeasurement.Value FROM TimeSeriesMeasurement JOIN Measurement ON TimeSeriesMeasurement.SignalID = Measurement.SignalID JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID ) SELECT FREQ.Timestamp, VPHA.Value AS VPhase, IPHA.Value AS APhase, FREQ.Value AS Frequencie FROM tsm FREQ JOIN tsm VPHA ON FREQ.Timestamp = VPHA.Timestamp JOIN tsm IPHA ON FREQ.Timestamp = IPHA.Timestamp WHERE FREQ.SignalType = ‘FREQ’ AND VPHA.SignalType = ‘VPHA’ AND IPHA.SignalType = ‘IPHA’ ORDER BY FREQ.Timestamp So can someone explain us how to use the above example? Like how many tables we need to create and what will be the columns of those tables. Or please suggest us if any other method is present. Thank You. Regards, Balaji TS ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 6, 2023, 3:53pm UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/2 "2023-11-06T15:53:47Z")
Hello Balaji TS, You can send multiple measurement streams to a single table. What you can’t do is define separate columns for each measurement stream in the `TimeSeriesMeasurement` table. I haven’t tried using PostgreSQL, but you should only need one table that looks something like this. ```sql CREATE TABLE TimeSeriesMeasurement ( SignalID NCHAR(36) NOT NULL, Timestamp VARCHAR(24) NOT NULL, Value DOUBLE PRECISION NOT NULL ) ``` The `SignalID` column is used to join the data with the `Measurement` table so you can determine which values belong to which measurement stream. The example query does that using a Common Table Expression so that it can be easily referenced multiple times in the query. To pivot the data so that each measurement stream gets its own column in the result set, the query joins the CTE with itself multiple times. I chose to use joins here because it should generally work regardless of the database engine you’re using. You may instead want to look into the `crosstab()` function in PostgreSQL for pivoting data. Thanks, Stephen ---
**Author:** ![BALAJI\_TS](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/balaji_ts/32/1277_2.png) [@BALAJI\_TS](http://discussions.gridprotectionalliance.org/u/BALAJI_TS)\ **Post date:** [November 7, 2023, 7:03am UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/3 "2023-11-07T07:03:41Z")
Hello Stephen, Thank You for the response. Actually we want each PMU measurement in seperate column because we wants access this table data from python code and need to process it. So we need all data in single table like. CREATE TABLE FinalPMUTable ( SignalID NCHAR(36) NOT NULL, (optinal column) Timestamp VARCHAR(24) NOT NULL, Volt\_Phase\_A DOUBLE NOT NULL, Volt\_Phase\_B DOUBLE NOT NULL, Volt\_Phase\_C DOUBLE NOT NULL, Volt\_Pos\_Seq DOUBLE NOT NULL, Angle\_Phase\_A DOUBLE NOT NULL, Angle\_Phase\_B DOUBLE NOT NULL, Angle\_Phase\_C DOUBLE NOT NULL, Angle\_Pos\_Seq DOUBLE NOT NULL, Frequencie DOUBLE NOT NULL, ROCOF DOUBLE NOT NULL, ) This how my final table looks like. So can we achive this using crosstab() function? I have tried using JOIN feature. But there is a drawback in this feature. We can join all the values from different table and we can view the joined data in query tool. But we cann’t get the joined into to seperate table. We want Joined data in seperate table, so that we can access single table in python code to get all the measurements of PMU. Is this possible in crosstab() function? I want all the measuremnt in seperate table and each type measurement should be in seperate coloum as mentioned above. Please provide us the solution for this. Regards, Balaji TS ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 8, 2023, 2:34pm UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/4 "2023-11-08T14:34:19Z")
Hello Balaji TS, The system cannot automatically put data into your `FinalPMUTable`. If you wanted that functionality from openPDC, you would need to implement a custom adapter that is capable of doing that. You can write a query that copies data from `TimeSeriesMeasurement` to `FinalPMUTable`. ```sql WITH tsm AS (...) INSERT INTO FinalPMUTable(...) SELECT ... FROM ... ``` However, assuming you can use Python to load data from a view instead of a table, you’d probably be better off doing that. Then you don’t have to worry about things like running the `INSERT` query periodically, dumping and refilling the table, or constraining the query to fill in only missing data. ```sql CREATE VIEW FinalPMUTable WITH tsm AS (...) AS SELECT ... FROM ... ``` I don’t actually know how the `crosstab()` function works. I only did a quick Google search to discover how to pivot data in PostgreSQL, and that function came up. Presumably, it’s both easier to use and performs better than doing multiple joins the way I’ve done it in my example. If you are able to create a view using `crosstab()` that you can load into your Python application, then that’s the way I would recommend doing it. Thanks, Stephen ---
**Author:** ![BALAJI\_TS](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/balaji_ts/32/1277_2.png) [@BALAJI\_TS](http://discussions.gridprotectionalliance.org/u/BALAJI_TS)\ **Post date:** [November 9, 2023, 11:58am UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/5 "2023-11-09T11:58:09Z")
Hello Stephen, I think using VIEW is good option for us. As I am new to PSQL database concepts, I am not able understand the below mentioned query clearly. CREATE VIEW FinalPMUTable WITH tsm AS (…) AS SELECT … FROM … What is tsm in the above query? Can you please write full Query, consider we have 3 tables as mentioned below. I want Final VIEW named as FinalPMUTable which contains value columns of all 3 tables. CREATE TABLE PMUFrequency ( SignalID NCHAR(36) NOT NULL, Timestamp VARCHAR(24) NOT NULL, Freq\_value DOUBLE PRECISION NOT NULL ) CREATE TABLE PMUVoltage ( SignalID NCHAR(36) NOT NULL, Timestamp VARCHAR(24) NOT NULL, Volt\_value DOUBLE PRECISION NOT NULL ) CREATE TABLE PMUAngle ( SignalID NCHAR(36) NOT NULL, Timestamp VARCHAR(24) NOT NULL, Angle\_value DOUBLE PRECISION NOT NULL ) Regards, Balaji ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 9, 2023, 1:57pm UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/6 "2023-11-09T13:57:51Z")
Hello Balaji, `tsm` is part of a Common Table Expression. It’s like a temporary view that exists only for one query. TBH, it’s not really my job to teach people how to use SQL, or to write SQL queries for them. I don’t mind helping you understand how to use our products, but I believe I’ve already given you enough information that you should be able to write the query yourself. So unfortunately, I’m going to have to refuse your request for a full SQL query. Instead, you can refer to the full query you referenced in your original post to assist you in writing your query. Thanks, Stephen ---
**Author:** ![BALAJI\_TS](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/balaji_ts/32/1277_2.png) [@BALAJI\_TS](http://discussions.gridprotectionalliance.org/u/BALAJI_TS)\ **Post date:** [November 10, 2023, 10:16am UTC](http://discussions.gridprotectionalliance.org/t/archiving-multiple-values-to-single-table-in-psql-database-using-ado-output-adapter/1212/7 "2023-11-10T10:16:31Z")
Hello Stephen, Thank You for the clarification. No Propblem. We are working on the PSQL query. Regards Balaji