Hi,
I have 14 tables filled with meteorological data, one record per parameter per hour. The id field holds the parameter
type(1=temperature, 2=humidity ...) My problem is that for short periods (maybe one week, one month) there are two
stationswith the same data, I mean the temperature of table1 is equal to the humidity of table3. I need to discover
thosecases.
I could pick one record in the first station and then compare it with the ones in the other tables for all the
parametersat that particular date. If two records are equals (it probably happens) I must then check the next one in
thetimeserie. If the second record is equal too, then probably the two series may be equals and I must raise an alert
frommy application. Is there a better and faster way to perform such a check ?
-- tables
CREATE TABLE table1
(
fulldate timestamp,
id smallint NOT NULL,
meanvalue real
) WITH (OIDS=FALSE);
--.....................
--.....................
CREATE TABLE table14
(
fulldate timestamp,
id smallint NOT NULL,
meanvalue real
) WITH (OIDS=FALSE);
--
-- inserts
insert into table1(select
('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
1::smallint, round(cast(random() as numeric), 1)::real
from generate_series(0,1000) as s(a)
);
--
insert into table2(select
('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
1::smallint, round(cast(random() as numeric), 1)::real
from generate_series(0,1000) as s(a)
);
--
-- same data as table 1 -- MUST BE FOUND BY THE CKECK ROUTINE
insert into table3(select fulldate, id, meanvalue from table1);
Thank in advance,
Paolo Saudin
