How can i delete a specific name from my postgres array

Question

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

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Dec 13, 2020 at 13:55

score 2 · Accepted Answer · 2020-12-13 14:07:36Z

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.

edited Dec 13, 2020 at 14:07

answered Dec 13, 2020 at 13:59

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

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

user330315 Over a year ago

Ah, sorry. I used the wrong operator (only works on JSON arrays). See my update

Collectives™ on Stack Overflow

How can i delete a specific name from my postgres array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related