How to clone array in PostgreSql?

Question

I have this snippet of code:

RAISE NOTICE 'p_new_roster: %', p_new_roster; --prints {3,4,5}
FOREACH i IN ARRAY p_new_roster
LOOP
  RAISE NOTICE 'p_new_roster[i]: %', p_new_roster[i]; --prints 5,NULL,NULL (through 3 iterations)
  v_new_users := v_new_users || p_new_roster[i];
END LOOP;

How come p_new_roster[1] is 5, p_new_roster[2] is NULL, etc.? Variable i is declared as integer, and arrays are declared as integer[].

Basically what I am trying to do is clone 'p_new_roster' array. If somebody knows better way, I would like to know how too.

PostgreSQL arrays are immutable. Why would you "clone" it? Just v_new_users = p_new_roster. What's the actual problem you're trying to solve by doing this, the "why" for the "how" you're asking here? — Craig Ringer
– Craig Ringer, Commented May 15, 2014 at 12:54
I have two arrays: arr1 = {1,2,3} and arr2 = {3,4,5} (this is just example). I need function that compares these two arrays and returns in one column {1,2} and in second {4,5} (I would put the result in composite type). — anagarD
– anagarD, Commented May 15, 2014 at 13:08
If I do v_new_users := p_new_roster then changes that I do with v_new_users would be seen in p_new_roster also? I need p_new_roster for later use. — anagarD
– anagarD, Commented May 15, 2014 at 13:10
@anagarD No. You assign the value of p_new to v_new not a reference to p_new — Clodoaldo Neto
– Clodoaldo Neto, Commented May 15, 2014 at 13:15
No, you've completely misunderstood how arrays work in PostgreSQL. They're immutable. It's not like Java where assigning the array just assigns the reference. The whole array is copied. It's like a string in Java. You can't modify an array in-place by setting attributes. — Craig Ringer
– Craig Ringer, Commented May 15, 2014 at 13:33

Clodoaldo Neto · Accepted Answer · 2014-05-15 13:18:00Z

1

If you are trying to take the nulls out of the array do

v_new_users := v_new_users || array_remove(p_new_roster, null)

without the loop

Or just append

v_new_users := v_new_users || p_new_roster

also without the loop

Or your script fixed

RAISE NOTICE 'p_new_roster: %', p_new_roster; --prints {3,4,5}
FOREACH i IN ARRAY p_new_roster
LOOP
  RAISE NOTICE 'p_new_roster[i]: %', i; 
  v_new_users := v_new_users || i;
END LOOP;

i is the array element value at each iteration not the array index

edited May 15, 2014 at 13:18

answered May 15, 2014 at 13:04

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

anagarD Over a year ago

I tought i was index -.- Thanks your answer helped a lot!

Collectives™ on Stack Overflow

How to clone array in PostgreSql?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related