Re: Check for duplicates before inserting new rows

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