How to replace a value in array

Question

I have a data like below.

id              col1[]
---             ------
1                {1,2,3}
2                {3,4,5}

My question is how to use replace function in arrays.

select array_replace(col1, 1, 100) where id = 1;

but it gives an error like:

function array_replace(integer[], integer, integer) does not exist

can anyone suggest how to use it?

Answer 1

Your statement (augmented with the missing FROM clause):

SELECT array_replace(col1, 1, 100) FROM tbl WHERE id = 1;

As commented by @mu, array_replace() was introduced with pg 9.3. I see 3 options for older versions:

1. intarray

As long as ...

• we are dealing with integer arrays.
• elements are unique.
• and the order of elements is irrelevant.

A simple and fast option would be to install the additional module intarray, which (among other things) provides operators to subtract and add elements (or whole arrays) from/to integer arrays:

SELECT CASE col1 && '{1}'::int[] THEN (col1 - 1) +  100 ELSE col1 END AS col1
FROM   tbl WHERE id = 1;

2. Emulate with SQL functions

A (slower) drop-in replacement for array_replace() using polymorphic types, so it works for any base type:

CREATE OR REPLACE FUNCTION f_array_replace(anyarray, anyelement, anyelement)
  RETURNS anyarray LANGUAGE SQL IMMUTABLE AS
'SELECT ARRAY (SELECT CASE WHEN x = $2 THEN $3 ELSE x END FROM unnest($1) x)';

Does not replace NULL values. Related:

• Replace NULL values in an array in PostgreSQL

If you need to guarantee order of elements:

• PostgreSQL unnest() with element number

3. Apply patch to source and recompile

Get the patch "Add array_remove() and array_replace() functions" from the git repo, apply it to the source of your version and recompile. May or may not apply cleanly. The older your version the worse are your chances. I have not tried that, I would rather upgrade to the current version.

Answer 2

You can create your own based on this source :

CREATE TABLE arr(id int, col1 int[]);

INSERT INTO arr VALUES (1, '{1,2,3}');
INSERT INTO arr VALUES (2, '{3,4,5}');

SELECT array(
SELECT CASE WHEN q = 1 THEN 100 ELSE q END 
FROM UNNEST(col1::int[]) q) 
FROM arr;

  array
-----------
 {100,2,3}
 {3,4,5}

You can create your own function and put it in your public schema if you still want to call by function though it will be slightly different than the original.

Answer 3

UPDATE tbl SET col1 = array_replace(col1, 1, 100) WHERE id = 1;

Answer 4

Here is the sample query for a test-array:

SELECT test_id,
       test_array,
       (array (
           -- Replace existing value 'int' of an array with given value 'Text'
           SELECT CASE WHEN a = '0' THEN 'MyEntry'
                       WHEN a = '1' THEN 'Apple'
                       WHEN a = '2' THEN 'Banana'
                       WHEN a = '3' THEN 'ChErRiEs'
                       WHEN a = '4' THEN 'Dragon Fruit'
                       WHEN a = '5' THEN 'Eat a Fruit in a Day'
                       ELSE 'NONE' END
           FROM UNNEST(test_array::TEXT[]) a) ::TEXT
           -- UNNEST : Lists out values of my_test_array
       ) test_result
FROM (
    --my_test_array
    SELECT 1 test_id, '{0,1,2,3,4,5,6,7,8,9}'::TEXT[][] test_array
) test;