Return to Answer

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

Allows to preserve the original order of elements by adding WITH ORDINALITY in a subquery. But this question does not ask for it. See:

• How to preserve the original order of elements in an unnested array?

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images; 

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images; 

There are a couple of basic techniques for the task:

• Select rows which are not present in other table