CODEWITHSUNDEEP
postgresql
GuiOm Clair
GuiOm Clair

Reputation: 149

PostgreSQL 9.5 Select only non matching records from two tables

I have three tables representing some geographical datas and : - one with the actual datas, - one storing the name of the streets, - one storing the combination between the street number and the street name (table address).

I already have some address existing in my table, in order to realize an INSERT INTO SELECT in a fourth table, I am looking on how to build the SELECT query to retrieve only the objects not already existing in the address table.

I tried different approaches, including the NOT EXISTS and the id_street IS NULL conditions, but I didn't manage to make it work.

Here is an example : http://rextester.com/KMSW4349

Thanks

Upvotes: 2

Views: 1788

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246308

You can simply use EXCEPT to remove the rows already in address:

INSERT INTO address(street_number,id_street)
   SELECT DISTINCT datas.street_number, street.id_street
   FROM datas
   LEFT JOIN street USING (street_name)
EXCEPT
   SELECT street_number, id_street FROM address;

You could end up with duplicates if there are concurrent data modifications on address.

To avoid that, you'd add a unique constraint and use INSERT ... ON CONFLICT DO NOTHING.

Upvotes: 2

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

Your sub query is not correct. You have to match with the outer tables:

INSERT INTO address(street_number,id_street)
SELECT DISTINCT street_number, id_street
FROM datas
LEFT JOIN street ON street.street_name=datas.street_name
WHERE NOT EXISTS (SELECT * FROM address a2 WHERE a2.street_number = datas.street_number AND a2.id_street = street.id_street);

Upvotes: 0

Related Questions