1

Hello everyone I just started on working with postgreSQL and i was wondering if there is a simple solution to delete a single value from an array. In the screenshot im trying to delete a single name from my array.

my PostgreSQL version: "PostgreSQL 12.4, compiled by Visual C++ build 1914, 64-bit"

here is what im trying to do

John
  • 97
  • 1
  • 7
  • 1
    Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. –  Dec 13 '20 at 13:55

1 Answers1

2

DELETE removes an entire row from your table.

You want to change the content of one column of an existing row in the table - for that you need to use UPDATE.

To remove a value from an array you the array_remove() function:

update test
   set name = array_remove(name, 'Jan')
where 'Jan' = any(name)

The where clause makes sure that only rows are updated that actually contain the value Jan in the array.

  • hey i tried your code but it gave me an error which i do not really understand. Could you help me out. the error is as follows. ERROR: operator does not exist: text[] ? unknown LINE 3: where name ? 'Jan' ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 64 – John Dec 13 '20 at 14:06
  • Ah, sorry. I used the wrong operator (only works on JSON arrays). See my update –  Dec 13 '20 at 14:07