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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to