'Moving PostgreSQL bigint array unique value to another index

How can I move the array bigint value from one index to another? For example, I have an array ARRAY[1, 2, 3, 4] of bigint unique values and want to move value 1 to index 3 so the final result would be ARRAY[2, 3, 1, 4]

The assumptions:

  1. Element in the array identified by the value.

  2. The uniqueness of the elements guaranteed.

  3. Any element can be moved to any place.

  4. Null values not involved on any side.

  5. The value is contained in the array if not we have 2 options. First is do nothing and second handling of this exception by exception mechanism. It's an extreme case that can happen only because of some BUG

  6. Arrays are 1-dimensional.

arrayspostgresql


Solution 1:[1]

General assumptions:

Simple solution

CREATE FUNCTION f_array_move_element_simple(_arr bigint[], _elem bigint, _pos int)
  RETURNS bigint[] LANGUAGE sql IMMUTABLE AS
'SELECT a1[:_pos-1] || _elem || a1[_pos:] FROM array_remove(_arr, _elem) a1'

All fine & dandy, as long as:

  • The given element is actually contained in the array.
  • The given position is between 1 and the length of the array.

Proper solution

CREATE FUNCTION f_array_move_element(_arr ANYARRAY, _elem ANYELEMENT, _pos int)
  RETURNS ANYARRAY AS
$func$
BEGIN
   IF _pos IS NULL OR _pos < 1 THEN
      RAISE EXCEPTION 'Target position % not allowed. Must be a positive integer.', _pos;
   ELSIF _pos > array_length(_arr, 1) THEN
      RAISE EXCEPTION 'Target position % not allowed. Cannot be greater than length of array.', _pos;
   END IF;

   CASE array_position(_arr, _elem) = _pos  -- already in position, return org
      WHEN true THEN
         RETURN _arr;
      WHEN false THEN                       -- remove element
         _arr := array_remove(_arr, _elem);
      ELSE                                  -- element not found
         RAISE EXCEPTION 'Element % not contained in array %!', _elem, _arr;
   END CASE;

   RETURN _arr[:_pos-1] || _elem || _arr[_pos:];
END
$func$  LANGUAGE plpgsql IMMUTABLE;

Exceptions are raised if any of the additional assumptions for the simple func are violated.

The "proper" function uses polymorphic types and works for any data type, not just bigint - as long as array and element type match.

db<>fiddle here

Solution 2:[2]

Postgresql supports slicing and appending so:

SELECT c, c[2:3] || c[1] || c[4:] AS result
FROM (SELECT ARRAY[1, 2, 3, 4] c) sub

db<>fiddle demo

Solution 3:[3]

Another variant using 'WITH .. SELECT ..' avoid searching elements by value, just array element numbers. jsonb[] with big jsons for example.

test_model.data - field to update. :idx_from, :idx_to - placeholders, 1-based.

  WITH from_removed AS (
    SELECT
      test_model.id,
      ARRAY_CAT(
        data[: :idx_from - 1],
        data[:idx_from + 1 :]
      ) AS "d"
    FROM test_model
  )
  UPDATE test_model AS source
  SET data =
    from_removed.d[: :idx_to - 1] ||
    data[:idx_from : :idx_from] ||
    from_removed.d[:idx_to :]
  FROM from_removed
    WHERE source.id = from_removed.id AND source.id = :id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Lukasz Szozda
Solution 3 Serge Rasnikov

Related Questions