Waiting for 9.3 – Add array_remove() and array_replace() functions.

On 11th of July, Tom Lane committed patch:

Add array_remove() and array_replace() functions. <span class="refs"> <span class="head" title="heads/master"><a href="/gitweb/?p=postgresql.git;a=shortlog;h=refs/heads/master">master
 
These functions support removing or replacing array element value(s)
matching a given search value.  Although intended mainly to support a
future array-foreign-key feature, they seem useful in their own right.
 
Marco Nenciarini and Gabriele Bartolini, reviewed by Alex Hunsaker

Array support in PostgreSQL is, to say it lightly, limited. They exist, and you can do stuff on them, but their scope is somewhat limited, and functionality in many places require unnesting and rebuilding array again.

This makes me very happy about new additions to array handling functions – these do not provide new functionality (I could, always, unnest, add a where/case, and pack back to array), but they'd make certain queries simpler to write and maintain.

So, let's see the demonstration:

$ SELECT array_remove('{a,b,c}'::text[], 'b');
 array_remove
──────────────
 {a,c}
(1 ROW)

Seems simple enough. Same query, without using new function:

$ SELECT array(
    SELECT x FROM unnest('{a,b,c}'::text[]) x WHERE x <> 'b'
);
 array
───────
 {a,c}
(1 ROW)

And the array_replace:

$ SELECT array_replace('{a,b,c}'::text[], 'b', 'x');
 array_replace
───────────────
 {a,x,c}
(1 ROW)

Same functionality without array_replace():

$ SELECT array(
    SELECT CASE WHEN q = 'b' THEN 'x' ELSE q END FROM unnest('{a,b,c}'::text[]) q
);
  array
─────────
 {a,x,c}
(1 ROW)

That's great. Of course it would be great to get something like intarray for arrays of all base types, but any addition to array handling routines is good. Thanks.

7 thoughts on “Waiting for 9.3 – Add array_remove() and array_replace() functions.”

  1. Curious about two cases; if there are multiple occurrences of an array value, I presume it replaces all occurrences? Is there any way to replace based on offset/position within the array?

  2. @Robert:

    $ SELECT array_replace('{a,b,b,b,c}'::text[], 'b', 'x');
 array_replace 
───────────────
 {a,x,x,x,c}
(1 ROW)

    To change just specific b, you’d have to use unnest and some window functions.

  3. and there are replace() function too.

    simple sql for simple array

    select array
    (
    select replace
    (
    unnest( array[ ‘abc’, ‘def’, ‘xyz’ ] ),
    ‘abc’,
    ‘123’
    )
    )

  4. @Misha:
    No. It cannot be used. Think what will happen if one of the array elements is “abcxxx”.

  5. Hi,I want to use the array_remove,array_replace function in other version of postgresql, is it possilbe ?

  6. @Francs:
    sure – get the patch from git repo, and apply it on sources for earlier Pg. It might not apply cleanly, but should be possible.

Comments are closed.