I have PostgreSQL 9.2 database and a table:
id integer,
allowed_types character varying(255)`
Sample data as below:
id allowed_types
1 3,4,5,13,14
How I can remove 4 and 5 from allowed_types
which is a comma separated varchar list?
After removing, the result should be allowed_types = 3,13,14
.
There are many records on that table, each allowed_types
could contain different numbers, separated by comma.
I considered string_to_array()
and array_remove()
, but array_remove()
is not in version 9.2, yet.
Best Answer
Proof of concept for regular expressions:
Returns:
Pad commas to cover corner cases at begin and end. The regular expression
',(4|5)(?=,)'
explained:,
... literal comma(4|5)
... two branches: either 4 or 5(?=,)
... positive lookahead: next character is a literal commaWould 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
(allowed_types)
:dbfiddle here
But I expect the first solution to be faster: complex regular expressions are more expensive.
The according
UPDATE
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.