Generate one additional row per array element

Question

Using PostgreSQL 9.6, I'm trying to insert rows into a table map, generated 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

in1 and each value in the array in2 should get entries for user name. I can't figure out how best to iterate through the array, whether it's a subselect, 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;

Erwin Brandstetter · Accepted Answer · 2022-09-07 19:44:54Z

1

Simply:

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

Assuming this table definition:

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

fiddle

Notably, in2 is an integer array (integer[]). Else, cast with in2::int[] - you have valid array literals on display.

About the element-to-array concatenation in in1 || in2, see:

How can the concatenatation of a text and a NULL value return a non-null result?

in1 and in2 can be NULL, but consider corner cases demonstrated in my fiddle. You may want to move unnest() to a LATERAL subquery to steer corner cases. See:

Flattening a relation with an array to emit one row per array entry

Careful with multiple set-returning functions like unnest() in the SELECT list, especially before Postgres 10. (Not an issue with just one.) See:

edited Sep 7, 2022 at 19:44

answered Sep 7, 2022 at 19:20

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Daniel Powley Over a year ago

should have mentioned in1 is int NOT NULL, and in2 is int[] (can be NULL). Returning an error that in1 is of type integer but expression is of type character varying (name of of type varchar, for the record)

Erwin Brandstetter Over a year ago

@DanielPowley: "in1 is int NOT NULL, and in2 is int[]" - then there is no varchar involved, and the error makes no sense. Should just work, as demonstrated in the fiddle (in pg 15 and pg 9.6 alike).

Daniel Powley Over a year ago

Tried again. This worked, looks like my ordering was wrong

mcg1103 · Accepted Answer · 2022-09-07 17:49:13Z

0

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

answered Sep 7, 2022 at 17:49

mcg1103

6981 gold badge4 silver badges9 bronze badges

Comments

Isolated · Accepted Answer · 2022-09-07 18:04:28Z

0

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.

answered Sep 7, 2022 at 18:04

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Collectives™ on Stack Overflow

Generate one additional row per array element

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related