CODEWITHSUNDEEP
sqlarrayspostgresql
Alex J
Alex J

Reputation: 59

postgres - how to fill a new array based on AND of two arrays

I am trying to create a new array based on comparing two arrays with a AND-like operator. See example below where I have two arrays, one is the template which NULL and NON-NULL values (whatever they are, any numeric or string, the point is they are not null) and the other the actual data array.

SELECT ARRAY[NULL, 1, NULL, 1, 1] as template;
SELECT ARRAY[2, 3] as data;

I want to create a new array that I fill with the 'data' array based on the 'template' array where there are non-null values (the number of non-null values in the template array might be different than that of the data array, the fill needs to go from left to right), so that the result will look like this:

{NULL,2,NULL,3,NULL}

Has anything done something similar to this?

Upvotes: 1

Views: 1501

Answers (1)

klin
klin

Reputation: 121604

Usually the simplest way for array manipulations is a plpgsql function, e.g.:

create or replace function fill_array_from_template(template anyarray, data anyarray)
returns anyarray language plpgsql as $$
declare
    i int;
    n int = 1;
begin
    for i in 1..array_length(template, 1) loop
        if template[i] is not null then
            template[i] := data[n];
            n:= n+ 1;
        end if;
    end loop;
    return template;
end $$;

select fill_array_from_template(array[null, 1, null, 1, 1], array[2, 3]);

 fill_array_from_template 
--------------------------
 {NULL,2,NULL,3,NULL}
(1 row)

Upvotes: 1

Related Questions