On 04/09/2020 14:21, Rich Shepard wrote:
This is a new issue for me: I've received data from a different source
and
need to add non-duplicates to two tables in the database. Each row in the
new data has a station ID and associated measurements.
The existing database includes a table for station information and
another
for measurements made there.
I want to learn which stations and which measurements in the new data set
are not included in the existing tables. And, I don't want to try
inserting
the new data and have postgres tell me when it's found duplicates,
especially since there are two tables involved.
My research into how to do this has not found a solution so I ask for
pointers to resources that will teach me how to add these new data to
both
tables.
Regards,
Rich
To insert data into both tables:
drop table if exists station;
drop table if exists measurement;
create table station (station_id serial,station_data text);
create table measurement (measurement_id bigserial,station_id
integer,ameasurement text);
insert into station(station_data)
values ('station1'),
('station2'),
('station3') ;
with src_data as (select station_data,ameasurement from (VALUES
('station1','meas1'),
('station2','meas2'),
('station3','meas3'),
('station4','meas4'))as
m(station_data,ameasurement)),
ins_station as (insert into station (station_data)
select station_data from src_data except select station_data from
station returning station_id,station_data )
insert into measurement (station_id,ameasurement)
select s.station_id,sd.ameasurement from src_data sd join (SELECT
station_id,station_data FROM ins_station UNION SELECT station_id,station_data
FROM station)s using (station_data)
except select station_id,ameasurement from measurement;
select * from station;
select * from measurement;
Regards,
Chris Sterritt
