Using EXCEPT clause in PostgreSQL

Question

I am trying to use the EXCEPT clause to retrieve data from table. I want to get all the rows from table1 except the one's that exist in table2. As far I understand, the following would not work:

CREATE TABLE table1(pk_id int, fk_id_tbl2 int);
CREATE TABLE table2(pk_id int);

Select fk_id_tbl2
FROM table1
Except
Select pk_id
FROM table2

The only way I can use EXCEPT seems to be to select from the same tables or select columns that have the same column name from different tables.

Can someone please explain how best to use the explain clause?

Ignaz

Answer 1

Your query seems perfectly valid:

SELECT fk_id_tbl2 AS some_name
FROM   table1
EXCEPT  -- you may want to use EXCEPT ALL
SELECT pk_id
FROM   table2;

Column names are irrelevant to the query. You just need the same number of columns, and their data types must match. The output column name of your query is fk_id_tbl2, just because it's the column name in the first SELECT. You can use any alias (some_name in my example).

What's often overlooked: the subtle differences between EXCEPT (which folds duplicates) and EXCEPT ALL - which keeps all individual unmatched rows. If there are no dupes to begin with, EXCEPT ALL is the better (faster) choice. See:

• Select rows which are not present in other table

• EXCEPT in the manual.

Ambur