How to Remove Elements of Array in Postgresql

How to remove specific value from Array?

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.

Remove array values in pgSQL

No, I don't think you can. At least not without writing something ugly like:

SELECT ARRAY (
 SELECT UNNEST(yourarray) LIMIT (
  SELECT array_upper(yourarray, 1) - 1
 )
)

Remove n elements from array using start and end index

You can access individual elements or ranges of elements:

If you e.g. want to remove elements 5 to 8, you can do:

select arr[1:4]||arr[9:]
from test;

or as an update:

update test
   set arr = arr[1:4]||arr[9:];

To remove the "first n elements", just use the slice after the n+1 element, e.g. to get remove the first 5 elements:

select arr[6:]
from test;

The syntax arr[6:] requires Postgres 9.6 or later, for earlier versions you need

select arr[6:cardinality(arr)]
from test;

cardinality() was introduced in 9.4, if you are using an even older version, you need:

select arr[6:array_lengt(arr,1)]
from test;

How to remove values from an array that exist in another array?

There are many ways.

First, you have to define whether there can be duplicates or NULL values in either of the arrays - and how to deal with those if possible. And whether to preserve original order of elements.

Assuming either is possible, and these are the rules:

Count duplicates separately. So if Values has five elements 'foo' and RemoveValues has three elements 'foo', thee will be two element 'foo' in the result.
Treat NULL values equal. So NULL can be removed with NULL (though in other contexts NULL = NULL yields NULL).
Order of array elements does not have to be preserved.
<SelectQuery> produces unique ID values.

That matches the behavior of standard SQL EXCEPT ALL. See:

Using EXCEPT clause in PostgreSQL

So:

SELECT t.*, sub.result_array
FROM   tbl t
JOIN   (<SelectQuery>) s USING ("ID")
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT unnest(t."Values")
      EXCEPT ALL
      SELECT unnest(s."RemoveValues")
      )
   ) sub(result_array);

About the LATERAL subquery:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Can be plain CROSS JOIN because an ARRAY constructor always produces a row.

You could wrap the functionality in a custom function:

CREATE FUNCTION f_arr_except_arr(a1 text[], a2 text[])
  RETURNS text[]
  LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT ARRAY (SELECT unnest(a1) EXCEPT ALL SELECT unnest(a2));
END;

Requires Postgres 14. See:

What does BEGIN ATOMIC ... END mean in a Postgresql SQL function / procedure?

For older (or any) Postgres versions, and for any array type:

CREATE OR REPLACE FUNCTION f_arr_except_arr(a1 anyarray, a2 anyarray)
  RETURNS anyarray
  LANGUAGE SQL IMMUTABLE PARALLEL SAFE AS
$func$
SELECT ARRAY (SELECT unnest(a1) EXCEPT ALL SELECT unnest(a2));
$func$;

db<>fiddle here

(PARALLEL SAFE only for Postgres 9.6 or later, though.)

Then your query can be something like:

SELECT *, f_arr_except_arr(t."Values", s."RemoveValues") AS result_values
FROM   tbl t
JOIN  (<SelectQuery>) s USING ("ID");

It's unclear whether you actually locked in CaMeL-case spelling with double-quotes. I assumed as much, but better you don't. See:

Are PostgreSQL column names case-sensitive?

And your UPDATE can be:

UPDATE tbl t
SET    "Values" = f_arr_except_arr(t."Values", s."RemoveValues")
FROM   <SelectQuery>) s
WHERE  s."ID" = t."ID"
AND    "Values" IS DISTINCT FROM f_arr_except_arr(t."Values", s."RemoveValues");

The additional WHERE clause is optional to avoid empty updates. See:

Return rows of a table that actually changed in an UPDATE
How do I (or can I) SELECT DISTINCT on multiple columns?

Remove an element in array based on condition in PostgreSQL

You an unnest and re-aggregate. The dates seem to be ordered, so:

select (select array_agg(dte order by dte)
        from (select dte, min(dte) over () as min_dte,
                     max(dte) over () as max_dte
              from unnest(my_dates) dte
             ) dte
        where dte not in (min_dte, max_dte) and
              extract(dow from dte) not in (2, 4)
       ) as new_my_dates
from my_table;

Note: If the dates are not ordered and you really want the first and last elements removed, then you can use unnest() with ordinality.

Is it possible to delete all values of an postgreSQL array in one query without using too many logical operators?

You can set the value to an empty array without specifying the type (or really, relying on implicit type conversion in Postgres):

update invitationtable 
    set inviteeaccepted = '{}'
    where reservationid = 99489;

I removed the single quotes from 99489 because presumably reservationid is a number.

Remove element from JSONB array in PostgeSQL

There is no built-in function for that. You can only remove array elements by position. The fact that you need something like this, indicates that your choice of de-normalizing your data model might have been a wrong decision. This would be a trivial DELETE operation on a properly normalized model.

However, you can easily write a function to remove an array element by value:

create function remove_element(p_array jsonb, p_to_remove int)
 returns jsonb
as
$$
  select jsonb_agg(a.element order by a.idx)
  from jsonb_array_elements(p_array) with ordinality as a(element, idx)
  where (a.element #>> '{}') <> p_to_remove::text;
$$
language sql
immutable;

The expression a.element #>> '{}' is necessary to convert the scalar jsonb value to a text value without having to deal with quotes in case the JSON array contains something different than plain integers. The function will fail with an error if you don't pass a JSON array.

Then you can use it like this:

UPDATE my_table 
  SET my_json_field = remove_elmeent(my_json_field, 549410);

Remove one, non-unique value from an array

Based on my old answer on dba.SE that you found and put to good use:

Delete array element by index

You might take it one step further:

CREATE OR REPLACE FUNCTION f_array_remove_elem1(anyarray, anyelement)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:idx-1] || $1[idx+1:] FROM array_position($1, $2) idx';

This function takes the value of the element to remove as 2nd parameter. Using the polymorphic pseudo-type anyelement accordingly to make this work for any array type.

Then the UPDATE simply is:

UPDATE test_table
SET    test_array = f_array_remove_elem1(test_array, 'B')
WHERE  id = 1;