How do combine two Queries or is it only one?

Question

Question:

How do combine two Queries or is it only one?

Example:

I have two exact similar tables my PostgreSQL Database. info_table and info_table_dump with the exact same columns date_at, name, color and length . Now i want to know if there are entries in info_table_dump that do not exist in info_table. Therefore i made these Query:

SELECT
date_at,name,color,length
FROM
info_table_dump
EXCEPT
SELECT
date_at,name,color,length
FROM
info_table;

The result is fine. It works like i assumed (i created 2 entries that do not match) the none-duplicates are shown. But now i wanted only to fetch the given ids of the non-duplicate rows. Something like this...

SELECT
id
FROM
info_table_dump
WHERE
(SELECT
date_at,name,color,length
FROM
info_table_dump
EXCEPT
SELECT
date_at,name,color,length
FROM
info_table);

I also tried something with EXISTS but its not the result i wanted.

So my Question how do combine the Query?

I want only the ìds of the info_table_dump rows, who not already exist in info_table.

Answer 1

So they should not exist in info_table?

SELECT id
FROM info_table_dump d
WHERE NOT EXISTS (
  SELECT 1
  FROM info_table i
  WHERE i.date_at IS NOT DISTINCT FROM d.date_at
    AND i.name IS NOT DISTINCT FROM d.name
    AND i.color IS NOT DISTINCT FROM d.color
    AND i.length IS NOT DISTINCT FROM d.length
);

The IS NOT DISTINCT FROM is just in case some columns are nullable.

A more conventional old-school method would be a left join and filtering the unmatched. Which might actually have better performance.

SELECT d.id
FROM info_table_dump d
LEFT JOIN info_table i
  ON i.date_at IS NOT DISTINCT FROM d.date_at
 AND i.name IS NOT DISTINCT FROM d.name
 AND i.color IS NOT DISTINCT FROM d.color
 AND i.length IS NOT DISTINCT FROM d.length
WHERE i.id IS NULL;

Test on db<>fiddle here