The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Check if all the Zip codes are valid.

Hey folks,
I have a list of Zips like this:
ZIP_LIST = '20109 20166 20601 20716 21014 21090 21093 21222 21236 21601 21704 21740 21784'
And I also have a Vertica table that has a Zips column. I want to check if there's a Zip in my list that isn't present in that Zips column, hence is invalid.
Is there a way to match a list with a column in the Vertica table?
0
Best Answer
-
mosheg Vertica Employee Administrator
Try this..
CREATE TABLE my_t1(f1 INT, f2 ARRAY[INT]); CREATE TABLE my_t2(f1 INT, ZIP INT); INSERT INTO my_t1 VALUES(1,ARRAY[20109,20166,20601,20716,21014,21090,21093,21222,21236,21601,21704,21740,21784]); COPY my_t2 FROM STDIN DELIMITER ',' ABORT ON ERROR; 1,20109 2,20166 3,20601 4,20716 5,21014 6,21090 7,21093 8,21222 9,21236 10,22222 11,21704 12,21740 13,21784 \. SELECT my_t2.f1, my_t2.ZIP, CONTAINS(my_t1.f2,my_t2.ZIP) FROM my_t1,my_t2; f1 | ZIP | CONTAINS ----+-------+---------- 0 | 20109 | t 1 | 20166 | t 2 | 20601 | t 3 | 20716 | t 4 | 21014 | t 5 | 21090 | t 6 | 21093 | t 7 | 21222 | t 8 | 21236 | t 9 | 11111 | f 10 | 21704 | t 11 | 21740 | t 12 | 22222 | f 13 | 33333 | f (14 rows) SELECT my_t2.ZIP AS 'ZIP codes not found in the array' FROM my_t1,my_t2 WHERE NOT CONTAINS(my_t1.f2,my_t2.ZIP); ZIP codes not found in the array ---------------------------------- 11111 22222 33333 (3 rows) WITH list1 AS (SELECT EXPLODE(my_t1.f2) OVER(PARTITION BEST) FROM my_t1) SELECT list1.position AS 'Array position', list1.value AS 'Invalid ZIP' FROM list1 LEFT JOIN my_t2 ON list1.value IN (my_t2.ZIP) WHERE my_t2.ZIP IS NULL; Array position | Invalid ZIP ----------------+------------- 9 | 21601 12 | 21784 (2 rows)
0