1

I have 2 tables that are related between them using integer data which is stored in arrays.

Data table

telemetria_data.historico_354677050164513

CREATE TABLE historico_354677050164513 (
    imei bigint NOT NULL,
    utc timestamp without time zone NOT NULL,
    clase integer NOT NULL,
    valor double precision[],
    registro timestamp with time zone DEFAULT now(),
    power integer DEFAULT (-1),
    voltaje double precision DEFAULT 0.0
);
ALTER TABLE ONLY historico
    ADD CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase) REFERENCES clase(id) ON UPDATE CASCADE ON DELETE CASCADE;

Sample data

      imei       |         utc         | clase |     valor      |           registro            | power | voltaje 
-----------------+---------------------+-------+----------------+-------------------------------+-------+---------
 354677050164513 | 2015-11-17 14:20:52 |    86 | {525,217,53,1} | 2015-11-17 14:21:04.238665+00 |     1 |       1

Structure table A

telemetria_data.clase 

CREATE TABLE clase (
    id integer NOT NULL,
    nombre character varying(100),
    estructura integer[],
    descripcion text
);

Sample data

 id |    nombre    |  estructura   | descripcion 
----+--------------+---------------+-------------
 86 | Caiquen - E4 | {35,34,20,21} | Telemetria

Structure table B

telemetria_data.clase_parametro;

CREATE TABLE clase_parametro (
    id integer NOT NULL,
    nombre character varying(50),
    descripcion text,
    unidad integer
);

Sample data

 id |                   nombre                   |                descripcion                 | unidad 
----+--------------------------------------------+--------------------------------------------+--------
 20 | Numero Energizacion                        | Numero Energizacion                        |      0
 21 | Numero de Partida                          | Numero de Partida                          |      0
 34 | Tiempo trayecto en que se inicia el evento | Tiempo trayecto en que se inicia el evento |     15
 35 | Duracion del Evento                        | Duracion del Evento                        |     15

Actual data is column 'valor' from table telemetria_data.historico_354677050164513, that table is related to table 'clase' using the clase and id columns, and 'clase' table is related to 'clase_parametro' using the estructura and id columns. (I hope this explanation is clear enough

Can you please provide hints on the needed JOIN to get data out of the "data table" with proper "column" names (from clase_parametro.nombre) for each of the items in the "historico_354677050164513.valor" column?

PS 1: I know normalization would help, but that's not actually doable in our scenario, where we already have tons of data stored.

PS 2: I've read several articles / questions regarding similar subjects, but could find a way to join all 3 tables.

  • Are you asking how to specify a matching condition between a value and an array of values? Have you tried SingleValueColumn IN (ArrayColumn)? – Andriy M Mar 25 '16 at 15:30
  • @AndriyM that's not useful in this scenario, as the 'valor' column from the 'historico' table has no direct relation with the other tables, it only preserves the array internal order of the clase.estructura column, that's why I think I need something to deal with arrays using indexes or something like that – Gonzalo Vasquez Mar 25 '16 at 17:04
  • Perhaps I'm misunderstanding this part of your explanation: "Actual data is column 'valor' from table telemetria_data.historico_354677050164513, that table is related to table 'clase' using the clase and id columns, and 'clase' table is related to 'clase_parametro' using the estructura and id columns." I thought that meant that historico_354677050164513.valor values were arrays of references to clase.id, and similarly clase.estructura values were arrays of references to clase_parametro.id. Did I indeed misunderstand that? – Andriy M Mar 25 '16 at 17:13
  • Yes @AndriyM you did misunderstood that. The valor column in no reference to no column, is has the actual data value, in the sample data provided the first array item in valor is 525, as it is the first index, it mean it's related to the first item in the estructura` column from the clase table, that's a 35 and that value DOES references id column from table clase_parametro (nombre='Duracion del Evento'). So it means that it has a 'Duracion del evento = 525'. Better explained now? – Gonzalo Vasquez Mar 26 '16 at 0:56
  • Ah yes, I think I get it now. historico_354677050164513.clase is a reference to clase.id. Each data item in the historico_354677050164513.valor array is related to an item in the corresponding position of clase.estructura. The items in the clase.estructura array are, in turn, references to clase_parametro.id. And you want the clase_parametro.nombre values to become captions (column headers) for the matching historico_354677050164513.valor items. Is that correct? – Andriy M Mar 26 '16 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.