CODEWITHSUNDEEP
sqlpostgresql
Diego Quirós
Diego Quirós

Reputation: 977

Select from literal values not in table in PostgreSQL?

I have some values in Excel, I copy the values and it look like this:

1 2 3 4

I want to write a query that select from above data that is not present in a table. Somethink like:

SELECT * FROM [1, 2, 3, 4] as foo where foo IS NOT ( SELECT id from table )

Supposing id column have 1,2,4 the query answer will be 3.

Preferably a query that can work in postgres and sql server

Thank you!

Upvotes: 14

Views: 8022

Answers (3)

adkisson
adkisson

Reputation: 465

You can try this

Select * from unnest(array[1,2,3,4]) as id

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

select *
from
    (values (1),(2),(3),(4)) v (id)
    left join
    t using (id)
where t.id is null

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You need a left join or something similar for this. Here is one method:

SELECT v.*
FROM (VALUES (1), (2), (3), (4)) v(foo) 
WHERE foo NOT IN ( SELECT id from table );

Upvotes: 18

Related Questions