Re: Check for duplicates before inserting new rows

Chris Sterritt <chris.sterritt@xxxxxxxxxx> · Fri, 4 Sep 2020 17:13:34 +0100



    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