1

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.

genichm
  • 525
  • 7
  • 18
  • Postgres arrays are 1-based, not 0-based like you seem to assume in your example. Also: how do you identify "value 1"? By index or by value? In the later case, is uniqueness of elements guaranteed? Or do you always move the *first* element of the array ? – Erwin Brandstetter Aug 16 '19 at 17:17
  • @ErwinBrandstetter thanks for the information, I identify the element by the value but can get the index too, the uniqueness of the elements guaranteed. I move any element in the array not only the first one. – genichm Aug 16 '19 at 18:25
  • Are we certain the value is contained in the array? What to do if not? Can `NULL` values be involved on either side? – Erwin Brandstetter Aug 16 '19 at 18:40
  • @ErwinBrandstetter 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. Null values not involved on any side. – genichm Aug 16 '19 at 20:03

3 Answers3

1

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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
Serge Rasnikov
  • 1