Re: Check for duplicates before inserting new rows

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