CODEWITHSUNDEEP
sqlarrayspostgresql
Azis_UA
Azis_UA

Reputation: 3

Delete arguments from array

There is table w/ colum called Cars in this colum I have array [Audi, BMW, Toyota, ..., VW]

And I want update this table and set Cars without few elements from this array (Toyota,..., BMW)

How can I get it, I want put another array and delete elements that matched

Upvotes: 0

Views: 117

Answers (3)

Sai Pardhu
Sai Pardhu

Reputation: 289

Maybe I Can help using pandas in python. Assuming, you'd want to delete all the rows having the elements you'd like to delete. Lets say df is your dataframe, then,

import pandas as pd

vals_to_delete = df.loc[(df['cars']== 'Audi') | (df['cars']== 'VW')]

df = df.drop(vals_to_delete)

or you could also do

df1 = df.loc'[(df['cars']!= 'Audi') | (df['cars']!= 'VW')]

In sql, you could use

DELETE FROM table WHERE Cars in ('Audi','VW);

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246348

You could call array_remove several times:

SELECT array_remove(
          array_remove(
             ARRAY['Audi', 'BMW', 'Toyota', 'Opel', 'VW'],
             'Audi'
          ),
          'BMW'
       );

   array_remove   
------------------
 {Toyota,Opel,VW}
(1 row)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can unnest the array, filter, and reaggregate:

select t.*,
       (select array_agg(car)
        from unnest(t.cars) car
        where car not in ( . . . )
       ) new_cars
from t;

If you want to keep the original ordering:

select t.*,
       (select array_agg(u.car order by n)
        from unnest(t.cars) with ordinality u(car, n)
        where u.car not in ( . . . )
       ) new_cars
from t

Upvotes: 1

Related Questions