If I consult certain data in a query where in , I would bring the data that match.
But my question is, how do I get the data that does not match and throw it in the query?
"Data to consult" is a random list, it is not a table, and of these data that I want to consult, I am not interested in those that are already there but those that are not.
To be able to select the rows of numbers that are desired, first ... well ... they have to be in a table. DATOS A CONSULTAR should be a table within the database.
If the values are used only for a casual query (or if they are generated from another script), a temporary table can be generated.
with datos_a_consultar ("NUMERO") AS (
VALUES (10), (15), (5), (4), (6), (20)
)
Allowing to use them as a result of the query. We could use NOT IN , but I prefer to use NOT EXISTS (which is more efficient if the column accepts nulls).
select "NUMERO"
from datos_a_consultar
where not exists (
select 1
from tabla
where tabla."NUMERO" = datos_a_consultar."NUMERO"
)
Note: I have not used PostgreSQL and surely this solution is not very efficient, or there are much simpler ways to do it.
A possible alternative to what recommends Mariano (personally I would opt for that) could be to create the temporary table < em> to flight , using the regexp_split_to_table function and passing the values as a string. You would still have a temporary table, but it would be somewhat closer to what you seem to be looking for.
The code would be something like this (you can see it running in this Rextester ):
SELECT *
FROM CAST(regexp_split_to_table('10,15,5,4,6,20', E',') AS integer) tabla2 (id)
WHERE id NOT IN ( SELECT "NUMERO" FROM tabla )