How to remove specific value from Array?

Question

I have a table which have a column that's storing the array values. I recently was asked to remove a particular value from array which I thought was a simple job but I ended up lost in complex functions and answers.

Here is the structure and the data of the table.

CREATE TABLE contacts (
   id serial PRIMARY KEY,
   name VARCHAR (100),
   phones TEXT []
);


INSERT INTO contacts (name, phones)
VALUES
  (
      'John Doe',
      '{"(408)-589-5846","(408)-589-5555"}'
   ),   
   (
      'Lily Bush',
      '{"(408)-589-5841"}'
   ),
   (
      'William Gate',
      '{"(408)-589-5842","(408)-589-58423"}'
   );

Here is the query which someone helped me to get the index values of the array, I was hoping I'd be able to somehow remove that value on the basis of this index but I couldn't find a way. So for instance I want to remove Phone number of John Doe at 2nd index, how can I do that by just providing the index number in where clause or in some other way ?

select c.name, 
       p.phone,
       p.idx
from contacts c
  left join lateral unnest(phones) with ordinality as p(phone, idx) on true;

Does this answer your question? [How to remove elements of array in PostgreSQL?](https://stackoverflow.com/questions/37917905/how-to-remove-elements-of-array-in-postgresql) — Renato, Jan 14 '20 at 13:25
@Renato not quite what I am looking for as this is not going to update the column values. Is it ? Also if it does how can I provide the index value to remove like 2 rows after unnesting might have same ID. — Muhammad Asim, Jan 14 '20 at 13:31

score 1 · Accepted Answer · 2020-01-14T14:15:04.353

You can get parts of an array by using a "slice" notation. phone[2:2] returns the second element, if you want to remove that you need to concatenate the slice "before" that and the slice "after" that, e.g. phones[1:1]||phones[3:], if you want to remove the 4th element, it would be phones[1:3]||phones[5:]

If you want to change the table, use UPDATE with that expression

update contacts
  set phones = phones[1:1]||phones[3:]
where name = 'John Doe';

You can put that into a function if you want to:

create function array_remove_index(p_input anyarray, p_index int)
  returns anyarray
as
$$
  select p_input[1:p_index - 1]||p_input[p_index + 1:];
$$
language sql;

update contacts
  set phones = array_remove_index(phones, 2)
where name = 'John Doe';

Requirements like that are usually a sign that the decision to de-normalize your model was not such a good choice (and the need to access things by index is an even strong indication for that).

Quote from the manual

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Arrays are sometimes quite convenient, but especially if you are new to SQL, relational database and Postgres specifically you should rather stick to the proven design principles of a well normalized data model.

How to remove specific value from Array?

1 Answers1