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
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
update table set "cars" = result.new_cars from ( select t.*, (select array_agg(car) from unnest(table.cars) car where car not in (...) ) new_cars from t ) as result where result.id = cars.idYou 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)
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);
