Postgresql – Column Not Like Any Value in Column in Other Table

postgresqlquerysubquery

The two tables in question are:

completed_types

id | type
---|--------
1  | XXX
2  | YYY

all_types:

id | type  | sub_type
---|-------|---------
1  | XXX X | aaa
2  | XXX-Y | bbb
3  | YYY-X | ccc
4  | YYY.Y | ddd
5  | QQQ-G | fff
6  | RRR+Q | ggg

What I want is to use the type column of completed_types to filter all_types.

Something like SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type FROM completed_types.

The expected output based on the above tables would be:

id | type  | sub_type
---|-------|---------
5  | QQQ-G | fff
6  | RRR+Q | ggg

However it does not seem to remove any results from the output. I tried appending a wildcard '%' to the end of the subquery:

SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type||'%' FROM completed_types)

But this has also not reduced the number of results.

My plan is to use this in a function, as all_types.type could grow quickly while completed_types.type will be slower, and will likely never be completely the same or even close, so if that would change your answer please let me know, but any help with this is appreciated.

Best Answer

While your solution should basically do the job:

SELECT type FROM all_types WHERE type NOT LIKE ALL(SELECT type||'%' FROM completed_types)

Note some corner-case problems:

  • If completed_types.type isn't defined NOT NULL and you introduce even a single NULL, your query will not return any rows. So make sure it's NOT NULL.

  • If all_types.type allows NULL, those rows are never returned. So make sure that one is NOT NULL as well or adapt the query like below.

  • If one of the special characters %\_ is allowed, you may want to escape their special meaning when transforming the string to a LIKE pattern. See:

I suggest (using the simple function f_like_escape() outlined in the link above):

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type LIKE f_like_escape(c.type) || %
   );

Also returns rows with all_types.type IS NULL - as opposed to your original.

Or save the readily escaped pattern as additional column in completed_types to avoid the escaping over and over.

Or, if completed_types.type always holds the leading 3 characters like your data sample suggests:

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type = left(c.type, 3)
   );
Related Question