Merging two arrays in PostgreSQL as index and value?

Question

Assuming I have two array fields in a table, like this:

 Column |   Type    |
--------+-----------+
 index  | integer[] | 
 value  | integer[] |

Where both index and value are equal in length, and the values in 'index' are guaranteed to be unique, for example:

SELECT * FROM ArrayTest;
   index   | value             
-----------+-----------------------------------
 {1,3,5,6} | {100, 103, 105, 106}

How would I make a query which returns a new array, where the values in 'index' are used as array indexes, and the values in 'value' become the values associated with the given index, i.e. something like:

SELECT some_array_function(index, value) as newarray;
            new_array
--------------------------------
{100, NULL, 103, NULL, 105, 106}

What I want to achieve is the same as array_combine in PHP.

This can be probably done in PLPGSQL. But this smells really bad (i.e. bad design) Are you sure you need to do inside your DB such a convoluted thing? (BTW, this is the same as array_combine only when the indexes are integers) — leonbloy
– leonbloy, Commented Jul 1, 2011 at 11:57
I agree that it smells. It's somewhat of a desperate attempt to optimize a read-bound application. The new array would be stored in a materialized view, which will only be used for doing certain analysis of the data where I think this design might be an improvement over the current situation. — simonfi
– simonfi, Commented Jul 1, 2011 at 12:31
I have a project that relies on arrays for one piece of functionality. There are only few array manipulation functions available in Postgres. I was surprised to find that in one case, using a PL/Python function was surprisingly easier and faster than the equivalent SQL. — jd.
– jd., Commented Jul 1, 2011 at 21:26

Matthew Wood · Accepted Answer · 2011-07-01 18:46:59Z

3

While overall a bad idea to store data like this, one way to do it is to use the unnest function and the process the results on the client side:

SELECT unnest(index), unnest(value) FROM ArrayTest;

 unnest | unnest 
--------+--------
      1 |    100
      3 |    103
      5 |    105
      6 |    106

If you must have a function to create a new array here's a short function that will do it:

CREATE FUNCTION array_stitch(index_array integer[], value_array integer[]) RETURNS integer[]
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
    ptr INTEGER;
    new_array INTEGER[];
BEGIN
    FOR ptr IN 1..array_upper(index_array,1) LOOP
            new_array[index_array[ptr]] := value_array[ptr];
    END LOOP;

    RETURN new_array;
END;
$$;

EDIT: Here's another way to do it:

SELECT array_agg(val order by idx) as output_array
FROM (SELECT generate_series(1,idx[array_upper(idx,1)]) from array_test) as g(idx)
LEFT JOIN (SELECT unnest(idx) as idx, unnest(val) as val FROM array_test\
    ) as data_points USING (idx);

edited Jul 1, 2011 at 18:46

answered Jul 1, 2011 at 17:05

Matthew Wood

16.5k6 gold badges49 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

simonfi Over a year ago

Thank you! The second approach is exactly what I was looking for.

Szymon Lipiński · Accepted Answer · 2011-07-01 16:50:36Z

1

Try this simple procedure:

CREATE OR REPLACE FUNCTION merge_arrays(index INTEGER[], value INTEGER[]) RETURNS INTEGER[] AS $$
DECLARE
    arr_size INTEGER;
    max_index INTEGER;
    result INTEGER[];
    i INTEGER;
    index_pos INTEGER := 1;
BEGIN
    arr_size := array_upper(index, 1);
    max_index := index[arr_size];

    FOR i IN 1..max_index LOOP
        IF index @> ARRAY[i] THEN
            result := result || value[index_pos];
            index_pos := index_pos + 1;
        ELSE
            result := result || '{NULL}'::INTEGER[];
        END IF;

    END LOOP;

    RETURN result;
END;
$$ LANGUAGE PLPGSQL;

Usage:

SELECT merge_arrays(index ,value) from array_test;

answered Jul 1, 2011 at 16:50

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

Collectives™ on Stack Overflow

Merging two arrays in PostgreSQL as index and value?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related