If I have 2 tables with identical schema and multiple unique columns, is there a way to show the conflicting rows between them?
create table table1 (id INTEGER not null primary key, col1 int not null, col2 int not null, unique( col1, col2 ));
create table table2 (id INTEGER not null primary key, col1 int not null, col2 int not null, unique( col1, col2 ));
insert into table1 values (1,1,1), (2,1,2), (3,1,3), (4,1,4);
insert into table2 values (5,2,1), (6,1,2), (7,2,3), (8,1,4);
Is there a query that will show me entries (*,1,2) and (*,1,4) are in conflict between the two tables?
One way that I could think of is to start a transaction then do an insert with ON CONFLICT... look at the results, then abort the transaction... but I'm hoping for something simpler.
An INNER JOIN on the columns would yield the same results.
SELECT * FROM table1 t1, table2 t2
WHERE t1.col1=t2.col1 AND t1.col2=t2.col2;