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.


4 Answers 4


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 ',(4|5)(?=,)' explained:

, ... 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 (allowed_types):

SELECT ltrim(regexp_replace('3,4,5,13,14', '((,|^)(4|5))(?=,|$)', '', 'g'), ',');

dbfiddle here

But I expect the first solution to be faster: complex regular expressions are more expensive.

The according UPDATE

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.

  • Thank you! This should work. Update will be later but data is required now... Jul 4, 2017 at 18:30
  • 2
    I'v confused a bit but SELECT trim(regexp_replace(',' || '3,4,5,13,14' || ',', ',(4|5),', ',', 'g'), ',') returns 3,5,13,14, not 3,13,14: dbfiddle
    – Abelisto
    Jul 4, 2017 at 18:34
  • 1
    @Abelisto: You are right, the answer (though accepted) had a bug. The regular expression "consumed" leading and trailing comma, so that consecutive matches would fail. I fixed it with a positive lookahead (available in pg 9.2) which does not consume the trailing comma. Jul 5, 2017 at 1:34
  • 1
    @Zhlobopotam: Consider the update. Jul 5, 2017 at 1:39
  • Actualy did not tested it yet. It seemed working. Thanks for fix. Jul 5, 2017 at 6:40

Postgres 9.2 allows you to use string_to_array and unnest, you can use it to get the desired result.

create table foo(id int, allowed_types varchar(256));
insert into foo values (1, '3,4,5,13,14');

1 rows affected
with a as
    select id, unnest(string_to_array(allowed_types, ',')) elem
    from foo
select id, string_agg(elem, ',')
from a 
where elem not in ('4','5')
group by id
id | string_agg
-: | :---------
 1 | 3,13,14   

dbfiddle here


intarray Extension

PostgreSQL ships with an extension that provides this and it's even faster than core array functionality on ints. You can use string_to_array and then intarray's - operator. Click here for the docs on intarray in 9.2

Before we install intarray.

test=# SELECT ARRAY[3,4,5,13,14] - ARRAY[4,5];
ERROR:  operator does not exist: integer[] - integer[]
LINE 1: SELECT ARRAY[3,4,5,13,14] - ARRAY[4,5];
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Install intarray and try again

test=# CREATE EXTENSION intarray;
test=# SELECT ARRAY[3,4,5,13,14] - ARRAY[4,5];
(1 row)

So from varchar.

SELECT string_to_array(allowed_types, ',')::int[] - ARRAY[4,5] AS after_operation
FROM ( VALUES ('3,4,5,13,14') ) AS t(allowed_types);

Other notes

I agree with @Erwin when he says you should probably use a normalized 1:n design. But even if you can't, it's very clear you do not want a csv in your column. Consider at least upgrading the type to int[]. This requires no change in schema and can be done very easily using

  ALTER COLUMN allowed_types
  USING string_to_array(allowed_types, ',')::int[];
  • Good option, but cannot install anything in production environment. Thank you for normalization tips! It legacy db, so it was made long time ago. Time to fix it. Cannot upvote however (newby here). Take +1 from me😃 Jul 4, 2017 at 18:35
  • @Zhlobopotam it's called an extension, but it's part of PostgreSQL. It's shipped in Ubuntu as postgresql-contrib. Essentially, you can build it separately and it's not required but it's a core module. Jul 4, 2017 at 19:22

You can still use two regexp in a row that will take care of everything, without needing to have a functional index:

-- Your update
-- taking out two numbers (4 and 5) from any place in the string
    allowed_type = regexp_replace(regexp_replace(allowed_type, '^4,|,4(,|$)', E'\\1', 'g'), '^5,|,5(,|$)', E'\\1', 'g')
    allowed_type ~  '^(4|5),|,(4|5)(,|$)'
    allowed_type ;
| allowed_type |
| :----------- |
| 3,13,14      |

See a step-by-step approach at dbfiddle here

Assumption: your list contains numbers separated by commas, with no spaces (after or before commas, nor at beggining or end of string). The numbers need not be in any order, and they can be repeated (even if it doesn't probably make sense)

  • Interesting. However, regular expressions are comparatively expensive. I added another alternative with a single regexp_replace on the plain column to my answer. Jul 5, 2017 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.