How to check if 2 series of data are equal

["Paolo Saudin" <paolo@xxxxxxxxxxx>]

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 stations with the same data, I mean the temperature of table1 is equal to the humidity of table3. I need to discover those cases.

I could pick one record in the first station and then compare it with the ones in the other tables for all the parameters at that particular date. If two records are equals (it probably happens) I must then check the next one in the timeserie. If the second record is equal too, then probably the two series may be equals and I must raise an alert from my 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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general