I need to return differences between two tables.
CREATE TEMPORARY TABLES
CREATE TEMP TABLE first(
zoom smallint NOT NULL,
x integer NOT NULL,
y integer NOT NULL
);
CREATE TEMP TABLE second(
zoom smallint NOT NULL,
x integer NOT NULL,
y integer NOT NULL
);
INSERT INTO first(zoom,x,y) VALUES(5,2,25);
INSERT INTO first(zoom,x,y) VALUES(5,4,45);
INSERT INTO first(zoom,x,y) VALUES(5,7,34);
INSERT INTO first(zoom,x,y) VALUES(5,45,40);
INSERT INTO first(zoom,x,y) VALUES(5,72,63);
INSERT INTO second(zoom,x,y) VALUES(5,2,25);
INSERT INTO second(zoom,x,y) VALUES(5,4,45);
INSERT INTO second(zoom,x,y) VALUES(5,7,34);
In table first there are extra rows:
5,45,40
5,72,63
zoom | x | y | first |second
------+----+--- +-------+------
5 | 45 | 40 | yes | no |
zoom | x | y | first |second
------+----+--- +-------+------
5 | 45 | 40 | yes | no |
5 | 45 | 40 | yes | no |
5 | 45 | 40 | yes | no |
zoom | x | y | first |second
------+----+--- +-------+------
5 | 45 | 40 | no | yes |
5 | 45 | 40 | no | yes |
5 | 45 | 40 | no | yes |
As you want to compare all columns of both tables, you can use a full outer join on all columns and the check if one of them is:
select case
when f.zoom is null then 'missing in first'
when s.zoom is null then 'missing in second'
end as status,
zoom, x, y
from "first" f
full outer join second s using (zoom, x, y)
where f.zoom is null or s.zoom is null;
The join based on using()
will return those columns that are not null (and only those columns - removing the duplicate columns from the result)
When using your sample data from the question, the result would be:
status | zoom | x | y
------------------+------+----+---
missing in second | 5 | 45 | 40
missing in second | 5 | 72 | 63
If a row in the second table is added that doesn't exist in the first, e.g.:
INSERT INTO second(zoom,x,y) VALUES(15,7,34);
then the result will be:
status | zoom | x | y
------------------+------+----+---
missing in second | 5 | 45 | 40
missing in second | 5 | 72 | 63
missing in first | 15 | 7 | 34