Proof of concept for regular expressions:
SELECT trim(regexp_replace(',' || '3,4,5,13,14' || ',', ',(4|5)(?=,)', '', 'g'), ',');
Pad commas to cover corner cases at begin and end. The regular expression
, ... literal comma
(4|5) ... two branches: either 4 or 5
(?=,) ... positive lookahead: next character is a literal comma
Would need a functional trigram index on
(',' || allowed_types || ',') for good performance with big tables (only slightly bigger than a regular index due to the added commas). Details:
Or, with a more sophisticated regular expression, you can work with the original column and a trigram index on just
SELECT ltrim(regexp_replace('3,4,5,13,14', '((,|^)(4|5))(?=,|$)', '', 'g'), ',');
But I expect the first solution to be faster: complex regular expressions are more expensive.
SET allowed_types = trim(regexp_replace(',' || allowed_types || ',', ',(4|5)(?=,)', '', 'g'), ',')
WHERE ',' || allowed_types || ',' ~ ',(4|5),'; -- here, plain comma is good
What I would really do:
Update to a current version of Postgres (pg 9.2 reaches EOL Sept 2017) and probably use a normalized 1:n design.