SQL - Update Table with all Values from Array-CodePudding

I'm trying to update a table "map" in postgreSQL 9.6 from another table "tbl"

tbl

name	in1	in2
a	1
b	2	{3}
c	4	{5,6}
d	7	{8,9,10}

should result in this

map

name	out1	out2
a	1	1
b	2	2
b	2	3
c	4	4
c	4	5
c	4	6
d	7	7
d	7	8
d	7	9
d	7	10

where col1 and each value in the array col2 should be given entries for user "name". I can't figure out how best to iterate through the array, whether its a sub select, or a built-in operator.

So far I have:

INSERT INTO map(name, out1, out2)
SELECT (name, in1, in1)
FROM
    tbl;

UPDATE map
SET out2 = 
    (loop possibly of in2?)
FROM
    tbl t
WHERE
     name = t.name;

Any help is appreciated

CodePudding user response：

insert into map (name, out1, out2)
select * from (
select name, int1, unnest(int2) int2 from tbl
union all
select name, int1, int1 from tbl) x

CodePudding user response：

Not sure which format your array is stored, but here's one method that works if it's a TEXT column. Using the split-to-table function combined with replace, then casting that to integer.

select *
from (
    select pname, in1 as out1, in1 as out2
    from tbl
    union
    select pname, in1 as out1,  
    nullif(regexp_replace(regexp_split_to_table(in2, ','), '\D', '','g'), '')::integer
    from tbl
  )z
where out2 is not null
order by 1,2

Output:

pname	out1	out2
a	1	1
b	2	2
b	2	3
c	4	4
c	4	5
c	4	6
d	7	7
d	7	8
d	7	9
d	7	10

Fiddle found here. Just wrap with an update statement.

CodePudding user response：

Simply:

INSERT INTO map (name, out1, out2)
SELECT pname, in1, unnest(in1 || in2)
FROM   tbl

Assuming this table definition:

CREATE TEMP TABLE tbl (
  pname text PRIMARY KEY
, in1   integer NOT NULL
, in2   integer[]  --  can be NULL?
);

fiddle