Question 114755 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Generate one additional row per array element

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;
Tags:

3

Answers


  1. 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
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 0

    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:

    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:

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search