Trabla: PostgreSQL: return values which not exists in table example.
Solving:
For example we have table tbl_cars with fields id , serialnumber , name
tbl_cars
---------------------
id | serialnumber | name
1 | XXX1 | BMW X5
2 | AAA2 | Toyota Prado
3 | CCC3 | Honda CR-V
And serial numbers we need to check - FFFF1, DDD3, XXX1, AAA2
Query should return only serial numbers which not exist in table
Result should be FFF1, DDD3
SELECT string_agg( serialnumber , ',' ) as result
FROM
unnest( ARRAY[ 'FFFF1', 'DDD3', 'XXX1', 'AAA2' ] ) AS serialnumber
WHERE
serialnumber NOT IN ( SELECT c.serialnumber from tbl_cars AS c )
Result:
FFF1, DDD3
No comments:
Post a Comment