Migrate an Oracle associative array to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL

The typical migration process for an Oracle database to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL requires both automated and manual procedures. The AWS Schema Conversion Tool (AWS SCT) can handle the automated duties of schema conversion. For specific database objects that can’t be automatically migrated, the manual duties entail post-schema AWS SCT migration touch-ups.

The majority of the custom code and the source database’s schema are automatically converted by AWS SCT to a format that is compatible with the target database. AWS SCT automates the transformation of Oracle PL/SQL code into similar PL/pgSQL code in PostgreSQL during a database migration from Oracle to PostgreSQL.

Associative arrays, also known as index-by tables in Oracle, provide a powerful and flexible way to manage key-value pairs efficiently in PL/SQL code. However, when migrating from Oracle to PostgreSQL, developers often face challenges due to the differences in their associative array implementations. PostgreSQL doesn’t have a direct equivalent to Oracle’s associative arrays. In this post, we explore the process of migrating associative arrays from Oracle to PostgreSQL, including alternative data structures and workarounds to achieve similar functionality.

Overview of Oracle associative arrays, PostgreSQL arrays, and Solution overview

An associative array is a set of key-value pairs where a key associates to a value. This association between a key and a value may be referred to as a mapping. The key is a unique index used to locate the associated value. The key can either be of a text or a integer type that can be mapped to any value type.

An associative array is appropriate for the following:

A relatively small lookup table, which can be constructed in memory each time you invoke the subprogram or initialize the package that declares it.
Passing collections to and from the database server.

PostgreSQL allows you to define a column to be an array of any valid data type including built-in type, user-defined type, enumerated type or composite type can be created. Additionally, PL/pgSQL in PostgreSQL allows us to declare a variable as an ARRAY type.

Migrating Oracle associative arrays to PostgreSQL requires a thoughtful approach because PostgreSQL doesn’t offer a direct equivalent data structure. However, PostgreSQL offers several alternative data structures to handle key-value pairs, such as arrays and JSONB. By using custom data types and arrays in PostgreSQL, we can effectively simulate the functionality of Oracle’s associative arrays. This post demonstrates a practical step-by-step example to help you understand and apply these concepts to your migration projects.

Prerequisites

To get started with the solutions described in this post, you need following:

An active AWS account
A source Oracle database (on premises or Amazon RDS for Oracle)
A target Amazon Aurora PostgreSQL-compatible edition or Amazon RDS PostgreSQL
A database user with SELECT privileges on the target database

Iterate over an associative array

In this section, we compare the approaches with Oracle and PostgreSQL to iterate over an associative array.

Oracle approach

The following is an example of Oracle code to loop through an associative array:

DECLARE
  TYPE varchar_assoc_array_type IS TABLE OF 
       VARCHAR2(100) INDEX BY VARCHAR2(100);
  varchar_assoc_array varchar_assoc_array_type;
  current_key VARCHAR2(100);
  first_key VARCHAR2(100);
BEGIN
  -- Populate the associative array with key-value pairs
  
  varchar_assoc_array('Key 1') := 'Value 1';
  varchar_assoc_array('Key 2') := 'Value 2';
  varchar_assoc_array('Key 3') := 'Value 3';

  -- Loop through the associative array
  
  DBMS_OUTPUT.PUT_LINE('*** Loop through the associative array ***');
  
  current_key := varchar_assoc_array.FIRST;

  WHILE current_key IS NOT NULL LOOP

  -- Retrieve the value associated with the current key
DBMS_OUTPUT.PUT_LINE('Key: ' || current_key || ', Value: ' || varchar_assoc_array(current_key));
      current_key := varchar_assoc_array.NEXT(current_key);
  END LOOP;
END;
/

Output:

Statement processed.
*** Loop through the associative array ***
Key: Key 1, Value: Value 1
Key: Key 2, Value: Value 2
Key: Key 3, Value: Value 3

In this example, we define varchar_assoc_array_type as an associative array indexed by VARCHAR2(100) keys to store the key-value pairs. We populate the associative array with three sample key-value pairs. We initialize the current_key variable with the first key in the associative array using the FIRST method. Then we enter a WHILE loop that continues as long as current_key is not NULL. Inside the loop, you can retrieve the corresponding value using varchar_assoc_array(current_key) and perform operations on the key-value pair.

After processing the current key, we update the current_key variable with the next key in the associative array using the NEXT method. This allows us to iterate through all the keys in the associative array.

PostgreSQL approach using an array

The following is the migration approach for PostgreSQL code to loop through an associative array.

First, we need to create a custom data type to simulate the associative array. We use a composite type that includes two attributes: index_col to store the keys and index_value to store the corresponding values:

CREATE TYPE varchar_assoc_array_type AS
(
    index_col  character varying(10),
    index_value character  varying(15)
);

Next, we populate the associative array in PostgreSQL using an array of the custom data type:

DO
$$
DECLARE
    v_index character varying := 'HYD';
    varchar_assoc_array varchar_assoc_array_type[];
    I RECORD;
BEGIN

    -- Populate the associative array with key-value pairs
    varchar_assoc_array := ARRAY[row('Key 1', 'Value 1'), row('Key 2', 'Value 2'), row('Key 3', 'Value 3')];

    -- Loop through the associative array
    RAISE NOTICE '*** Loop through the associative array ***';

    FOREACH i IN ARRAY varchar_assoc_array
    LOOP
        RAISE NOTICE 'KEY : % VALUE : % ', i.index_col, i.index_value;
    END LOOP;
END;
$$;

Output:

NOTICE: *** Loop through the associative array ***
NOTICE: KEY : Key 1 VALUE : Value 1
NOTICE: KEY : Key 2 VALUE : Value 2
NOTICE: KEY : Key 3 VALUE : Value 3
DO

In this example, we created a type named varchar_assoc_array_type and used the extra member attribute index_col to store index column values to simulate the functionality of Oracle’s associative arrays. We used the array_length function to iterate through the associative array.

PostgreSQL approach using JSONB

To use JSONB, we first create a wrapper function. This function is named as add_update_item_json_map, takes three parameters: an existing JSONB object named field_entity_map, a key named key_item, and a value named value_item. It returns a modified JSONB object with the key-value pair added or updated. The function uses the jsonb_set function to achieve this. See the following code:

CREATE OR REPLACE FUNCTION add_update_item_json_map
(
    	field_entity_map jsonb,
    	key_item         text,
    	value_item       text
)
    RETURNS jsonb
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
BEGIN   
    return jsonb_set(field_entity_map , ('{'|| key_item ||'}')::text[],('"' ||      value_item || '"') :: jsonb);     
END;
$BODY$;

We can use the wrapper function add_update_item_json_map to populate the associative array using the following logic:

DO $$
DECLARE
  field_entity_map jsonb  :='{}' ;
BEGIN
  RAISE NOTICE 'Jsonb processed.'; 
  
-- Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , 'Key1'::text,'Value1'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 'Key 2'::text,'Value 2'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 'Key 3'::text,'Value 3'::text)::jsonb;

-- Look through the associative array

RAISE NOTICE '*** Associative array Key:Value *** %',     field_entity_map;
END;
$$;

Output:

NOTICE: Jsonb processed.
NOTICE: *** Associative array Key:Value *** {“Key1”: “Value1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}
DO
Query returned successfully in 53 msec.

In this example, we populate the field_entity_map JSONB object by calling the add_update_item_json_map function with different key-value pairs. This function adds or updates each key-value pair in the JSONB object.

Find the first and last elements and get the current size of an associative array

In this section, we compare the approaches with Oracle and PostgreSQL to find the first and last elements and get the current size of an associative array.

Oracle approach

The following is the Oracle code to find the first and last elements in the associative array

DECLARE
TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  	varchar_assoc_array varchar_assoc_array_type;
  	first_key VARCHAR2(100);
  	last_key VARCHAR2(100);
  	max_size NUMBER; 
BEGIN
  -- Populate the associative array with key-value pairs
  
  	varchar_assoc_array('Key 1') := 'Value 1';
  	varchar_assoc_array('Key 2') := 'Value 2';
  	varchar_assoc_array('Key 3') := 'Value 3';

-- Find the first and last elements in the associative array

DBMS_OUTPUT.PUT_LINE('*** Find the first and last elements in the associative array ***');
  
  first_key := varchar_assoc_array.FIRST;
  last_key := varchar_assoc_array.LAST;

  -- Output the first and last key-value pairs
DBMS_OUTPUT.PUT_LINE('First Key: ' || first_key || ', Value: ' ||  varchar_assoc_array(first_key));

DBMS_OUTPUT.PUT_LINE('Last Key: ' || last_key || ', Value: ' || varchar_assoc_array(last_key));

  -- Get the current size of the associative array
  
  DBMS_OUTPUT.PUT_LINE('*** Get the current size of the associative array ***');
  max_size := varchar_assoc_array.COUNT;

  -- Output the size
  DBMS_OUTPUT.PUT_LINE('Size of the Associative Array: ' || max_size );

END;
/

Statement processed.
*** Find the first and last elements in the associative array ***
First Key: Key 1, Value: Value 1
Last Key: Key 3, Value: Value 3
*** Get the current size of the associative array ***
Size of the Associative Array: 3

In this example, we use the FIRST and LAST collection methods. The FIRST method retrieves the first key, and the LAST method retrieves the last key in the associative array. Finally, we output the key-value pairs associated with the first and last keys. Similarly, we use the COUNT method to find the maximum size of an associative array.

PostgreSQL approach using array

The following is the PostgreSQL code to find the first and last elements in the associative array:

DO
$$
DECLARE
    v_index             character varying :='HYD';
    varchar_assoc_array varchar_assoc_array_type[];
    first_key           integer;
    last_key            integer;
BEGIN

    -- Populate the associative array with key-value pairs
    
    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

    -- Find the first and last elements in the associative array

    RAISE NOTICE '*** Find the first and last elements in the associative array ***';
  
    	first_key := coalesce(array_lower(varchar_assoc_array,1),0);
    	last_key  :=  coalesce(array_upper(varchar_assoc_array,1),0);

    -- Output the first and last key-value pairs
    
RAISE NOTICE 'First Key: % , Value: % '    ,varchar_assoc_array[first_key].index_col , varchar_assoc_array[first_key].index_value;

RAISE NOTICE 'Last Key: % , Value: % ' , varchar_assoc_array[last_key].index_col , varchar_assoc_array[last_key].index_value; 
END;
$$

Output:

NOTICE: *** Find the first and last elements in the associative array ***
NOTICE: First Key: Key 1 , Value: Value 1
NOTICE: Last Key: Key 3 , Value: Value 3
DO

In PostgreSQL, we use the array_lower and array_upper methods. The array_lower method returns the lower bound (index) of the array, and the array_upper method returns the upper bound (index). Because PostgreSQL arrays are 1-based (unlike Oracle’s 0-based associative arrays), we use the coalesce function to handle empty arrays and set default values of 0. We also used the array_length method to find the maximum length of an array.

PostgreSQL approach using JSONB

To use JSONB, we need the wrapper function add_update_item_json_map created in the previous step.

Then we create a new wrapper function named get_item_json_map, which retrieves the value associated with a given key from a JSONB object. It takes two parameters: the JSONB object (field_entity_map) and the key (key_item). It returns the value associated with the specified key as text. See the following code:

CREATE OR REPLACE FUNCTION get_item_json_map(
field_entity_map jsonb,
key_item text
)
    RETURNS text
    LANGUAGE 'plpgsql'
  AS $BODY$
  DECLARE
  BEGIN
      	RETURN   field_entity_map->>(key_item); 
   END;
$BODY$;

The following code shows the main logic :

DO $$
DECLARE
    field_entity_map jsonb := '{}';
    keys text[];
    first_key text;
    last_key text;
    first_value text;
    last_value text;
    key_value text;
    array_size integer;
BEGIN
    
    -- Populate the JSONB object with key-value pairs
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 1'::text, 'Value 1'::text)::jsonb;
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 2'::text, 'Value 2'::text)::jsonb;
    field_entity_map = add_update_item_json_map(field_entity_map, 'Key 3'::text, 'Value 3'::text)::jsonb;

    -- Loop through the keys and populate the keys array
    FOR key_value IN SELECT jsonb_object_keys(field_entity_map)
    LOOP
        keys := keys || key_value;
    END LOOP;


    -- Get the first and last keys
    first_key := keys[1];
    last_key := keys[array_upper(keys, 1)];

    -- Get the values corresponding to the first and last keys
    first_value := get_item_json_map(field_entity_map, first_key);
    last_value := get_item_json_map(field_entity_map, last_key);

    -- Output results
	
    RAISE NOTICE '*** Find the first and last elements in the associative array ***';
    RAISE NOTICE 'First Key: %, Value: %', first_key, first_value;
    RAISE NOTICE 'Last Key: %, Value: %', last_key, last_value;

END;
$$;

In this example, we obtained the first key from the sorted keys array using keys[1] and obtained the last key from the sorted keys array using keys[array_upper(keys, 1)]. The get_item_json_map function fetches the values associated with the first and last keys.

Find if an index exists in an associative array and update its index value

In this section, we compare the approaches in Oracle and PostgreSQL to find if an index exists in an associative array and update its index value.

Oracle approach

The following is the Oracle code to check if a specific key exists in the array and update the value associated with that key if it exists:

DECLARE
TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
      varchar_assoc_array varchar_assoc_array_type;
      current_key VARCHAR2(100);

BEGIN
  -- Populate the associative array with key-value pairs
  
  varchar_assoc_array('Key 1') := 'Value 1';
  varchar_assoc_array('Key 2') := 'Value 2';
  varchar_assoc_array('Key 3') := 'Value 3';

 -- Check if a specific key exists in the associative array and Update only if it exists 
  
  IF varchar_assoc_array.EXISTS('Key 2') THEN

     DBMS_OUTPUT.PUT_LINE('*** Key 2 exists with Value : ' ||       varchar_assoc_array('Key 2'));

     DBMS_OUTPUT.PUT_LINE('*** Update a specific element in the associative array ***');
     varchar_assoc_array('Key 2') := 'New Value 2';
     DBMS_OUTPUT.PUT_LINE('*** Key 2 updated with Value : ' ||   varchar_assoc_array('Key 2'));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Key 2 does not exist in the associative array.');
  END IF;

END;
/

Output:

Statement processed.
*** Key 2 exists with Value : Value 2
*** Update a specific element in the associative array ***
*** Key 2 updated with Value : New Value 2

We use the EXISTS method of the associative array to check if ‘Key 2’ exists in the array. We then update the value associated with ‘Key 2’ to ‘New Value 2‘ using the assignment statement varchar_assoc_array(‘Key 2’) := ‘New Value 2’.

PostgreSQL approach using array

The following is the PostgreSQL code to achieve the same objective:

DO
$$
DECLARE
v_index character varying :='HYD';
varchar_assoc_array varchar_assoc_array_type[];
v_pos int;
BEGIN

    -- Populate the associative array with key-value pairs
    
    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];
    
      -- Check if a specific key exists in the associative array and Update only if it exists 
    IF EXISTS (
SELECT * FROM unnest(varchar_assoc_array) a  WHERE a.index_col='Key 2'
 ) 
    THEN
raise notice '*** Update a specific element in the associative array ***';
    
       SELECT a.v_rnum into v_pos from (
                                        SELECT *,row_number() over () v_rnum 
from unnest(varchar_assoc_array)
                                    ) a where a.index_col='Key 2';
                                    
raise notice '*** Key 2 exists with Value : %',         varchar_assoc_array[v_pos].index_value;

varchar_assoc_array[v_pos].index_value := 'New Value 2';
    
raise notice '*** Key 2 updated with Value :  %', 
varchar_assoc_array[v_pos].index_value;
    
    ELSE
        RAISE NOTICE 'Key 2 does not exist in the associative array.';
    END IF;
    
END;
$$;

Output:

Statement processed.
*** Key 2 exists with Value : Value 2
*** Update a specific element in the associative array ***
*** Key 2 updated with Value : New Value 2

In this PostgreSQL example, we mimic the behavior of an associative array similar to the one demonstrated in the Oracle PL/SQL example. To locate the position of ‘Key 2‘ in the array, we use the unnest function with the row_number() window function and store the result in the variable v_pos. If ‘Key 2‘ exists, we enter the IF block. We then update the value associated with ‘Key 2‘ in the associative array at position v_pos to ‘New Value 2‘.

PostgreSQL approach using JSONB

To use JSONB, we need the add_update_item_json_map wrapper function that we created earlier. This function allows us to add or update key-value pairs in a JSONB object.

Then we call the add_update_item_json_map function to update the value:

DO $$
DECLARE
  field_entity_map jsonb  :='{}' ;
BEGIN
  RAISE NOTICE 'Jsonb processed.';   
-- Populate the associative array with key-value pairs
field_entity_map = add_update_item_json_map(field_entity_map , 
'Key 1'::text,'Value 1'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 
'Key 2'::text,'Value 2'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 
'Key 3'::text,'Value 3'::text)::jsonb;
-- Look through the associative array
RAISE NOTICE '*** Look through the associative array *** %',  field_entity_map;

-- Check if a specific key exists in the JSONB object and update only if it exists
  	RAISE NOTICE '*** Update a specific element in the JSONB object ***';

  	-- Check if 'Key 2' exists in the JSONB object
  	IF field_entity_map ? 'Key 2' THEN
field_entity_map = add_update_item_json_map(field_entity_map, 'Key 2', 'New Value 2')::jsonb;
RAISE NOTICE '*** Key 2 updated, and the JSONB object now has New Value of key 2 *** %', field_entity_map;
  	ELSE
    		RAISE NOTICE '*** Key 2 does not exist in the JSONB object ***';
  	END IF;
END;
$$;

Output:

NOTICE: Jsonb processed.
NOTICE: *** Look through the associative array *** {“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}
NOTICE: *** Update a specific element in the JSONB object ***
NOTICE: *** Key 2 updated, and the JSONB object now has New Value of key 2 *** {“Key 1”: “Value 1”, “Key 2”: “New Value 2”, “Key 3”: “Value 3”}
DO

In this example, we called add_update_item_json_map function to update the value of key ‘Key 2‘ from ‘Value 2‘ to ‘New Value2’ in the field_entity_map JSONB object.

Insert a new element into the associative array

In both the Oracle and PostgreSQL examples, we demonstrate how to insert a new element into an associative array. However, the approaches differ due to the nature of associative arrays in each database system.

Oracle approach

The following is the Oracle code to insert an element into an associative array:

DECLARE
  TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  varchar_assoc_array varchar_assoc_array_type;
  current_key VARCHAR2(100);
BEGIN
  -- Populate the associative array with key-value pairs
  
   varchar_assoc_array('Key 1') := 'Value 1';
   varchar_assoc_array('Key 2') := 'Value 2';
   varchar_assoc_array('Key 3') := 'Value 3';

  -- Insert a new element into the associative array
  DBMS_OUTPUT.PUT_LINE('*** Insert a new element into the associative array ***');
  varchar_assoc_array('Key 4') := 'Value 4';
 DBMS_OUTPUT.PUT_LINE('*** Newly inserted Key 4  Value : ' || varchar_assoc_array('Key 4'));

END;

Output:

Statement processed.
*** Insert a new element into the associative array ***
*** Newly inserted Key 4 Value : Value 4

In this example, we use the assignment statement varchar_assoc_array(‘Key 4’) := ‘Value 4’. We output the newly inserted key and its associated value using the DBMS_OUTPUT.PUT_LINE statement.

PostgreSQL approach using array

The following is the PostgreSQL code to insert an element into an associative array:

DO
$$
DECLARE
v_index character varying :='HYD';
varchar_assoc_array varchar_assoc_array_type[];
v_pos int;
BEGIN

    -- Populate the associative array with key-value pairs
    
    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];

    RAISE NOTICE ' *** Insert a new element into the associative array *** ';
    
    varchar_assoc_array[coalesce(array_length(varchar_assoc_array,1),0)+1] := row('key 4','Value 4');
    
   -- in postrgreSQL we need to find the position of "Key 4" index , before priniting its value

     SELECT a.v_rnum into v_pos from (
                                        SELECT *,row_number() over () v_rnum 
    from unnest(varchar_assoc_array)
                      ) a where a.index_col='key 4';

  RAISE NOTICE '*** Newly inserted Key 4  Value : %' ,      varchar_assoc_array[v_pos].index_value;
    
END;
$$;

Output:

Statement processed.
*** Insert a new element into the associative array ***
*** Newly inserted Key 4 Value : Value 4

To insert a new element into the associative array, we use array indexing. We calculate the position for the new element using coalesce(array_length(varchar_assoc_array,1),0)+1, which gives us the next available position in the array. We then assign the new key-value pair row(‘key 4′,’Value 4’) to that position.

To retrieve the newly inserted key-value pair, we use a subquery with the unnest function and row_number() window function to find the position of ‘Key 4‘ in the array. We store this position in the variable v_pos.

PostgreSQL approach using JSONB

We need the add_update_item_json_map wrapper function that we created earlier to add or update key-value pairs in a JSONB object.

We call the add_update_item_json_map function to insert a new key-value pair into the field_entity_map JSONB object:

DO $$
DECLARE
field_entity_map jsonb  :='{}' ;
BEGIN
RAISE NOTICE 'Jsonb processed.';  
 
-- Populate the associative array with key-value pairs
field_entity_map = add_update_item_json_map(field_entity_map , 'Key 1'::text,'Value 1'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 2'::text,'Value 2'::text)::jsonb;

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 3'::text,'Value 3'::text)::jsonb;

-- Look through the associative array

RAISE NOTICE '*** Look through the associative array *** %',  field_entity_map;

-- Check if a specific key exists in the associative array and Update only if it exists 

  RAISE NOTICE '*** Add a new element in the associative array ***';

  field_entity_map = add_update_item_json_map(field_entity_map,'Key 2','New Value 2')::jsonb;  

RAISE NOTICE '*** Key 2 updated and the associative array now has New Value of key 2  *** %',  field_entity_map;
END;
$$

Output:

NOTICE: Jsonb processed.
NOTICE: *** Look through the associative array ***
{“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}
NOTICE: *** Add a new element in the associative array ***
NOTICE: *** Key 2 updated and the associative array now has New Value of key 2 *** {“Key 1”: “Value 1”, “Key 2”: “New Value 2”, “Key 3”: “Value 3”}
DO

In this example, we called the add_update_item_json_map function to insert a new element with the key ‘Key 4’ and ‘Value 4‘ into the field_entity_map JSONB object.

Delete an element from the associative array

In this section, we compare the approaches with Oracle and PostgreSQL to delete an element from an associative array.

Oracle approach

The following is the Oracle code to delete an element from an associative array:

DECLARE
  TYPE varchar_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  varchar_assoc_array varchar_assoc_array_type;
  current_key VARCHAR2(100);
BEGIN
  -- Populate the associative array with key-value pairs
  
   varchar_assoc_array('Key 1') := 'Value 1';
   varchar_assoc_array('Key 2') := 'Value 2';
   varchar_assoc_array('Key 3') := 'Value 3';

  DBMS_OUTPUT.PUT_LINE(' *** Delete a specific element from the associative array ***');
  -- Delete a specific element from the associative array
  varchar_assoc_array.DELETE('Key 2');

   -- Loop through the associative array to confirm the deletion
  current_key := varchar_assoc_array.FIRST;
  WHILE current_key IS NOT NULL LOOP
    -- Retrieve the value associated with the current key
    DBMS_OUTPUT.PUT_LINE('Key: ' || current_key || ', Value: ' || varchar_assoc_array(current_key));
    current_key := varchar_assoc_array.NEXT(current_key);
  END LOOP;

END;
/

Output:

Statement processed.
*** Delete a specific element from the associative array ***
Key: Key 1, Value: Value 1
Key: Key 3, Value: Value 3

In Oracle, to delete a specific element from the associative array, we use the DELETE method with the key we want to remove. In this case, we delete the element with the key ‘Key 2‘.

PostgreSQL approach using array

The following is the PostgreSQL code, in which we create a helper function named array_rm to delete an element from an associative array.

First, we create the wrapper function to delete the key value:

CREATE OR REPLACE FUNCTION array_rm(
    p_input anyarray,
    p_index integer,
    INOUT p_new_val anyarray)
    RETURNS anyarray
    LANGUAGE 'plpgsql'
AS $BODY$
Declare
 j NUMERIC:=0;
begin   
    For i in 1 .. coalesce(array_length(p_input,1),0) Loop 
      IF i != p_index THEN 
        j:= j+1;
        p_new_val[j] := p_input[i];
      END IF; 
    End Loop; 

end;
$BODY$;

This function takes an input array, the index of the element to be removed, and an output array (passed as INOUT). The function iterates through the input array, excluding the element at the specified index, and populates the output array with the remaining elements. Next, we use the following code:

DO
$$
DECLARE
v_index character varying :='HYD';
varchar_assoc_array varchar_assoc_array_type[];
varchar_assoc_array_local varchar_assoc_array_type[];
v_pos int;
BEGIN

    -- Populate the associative array with key-value pairs
    
    varchar_assoc_array := ARRAY[row('Key 1','Value 1'),row('Key 2','Value 2'),row('Key 3','Value 3')];
    
-- indentify the position of Key 2 
    SELECT a.v_rnum into v_pos from (
                                        SELECT *,row_number() over () v_rnum from unnest(varchar_assoc_array)
                                    ) a where a.index_col='Key 2';
    
    RAISE NOTICE ' *** Delete a specific element from the associative array using array_rm helper function ***';
    
    select array_rm(
                    varchar_assoc_array,
                    v_pos,
                    null) 
    into varchar_assoc_array_local;
    
    FOR i in 1..coalesce(array_length(varchar_assoc_array_local,1),0) 
    LOOP
RAISE NOTICE 'KEY : % VALUE : % ', varchar_assoc_array_local [i].index_col, varchar_assoc_array_local [i].index_value;
    END LOOP;    
END;
$$

Output:

NOTICE: *** Delete a specific element from the associative array using array_rm helper function ***
NOTICE: KEY : Key 1 VALUE : Value 1
NOTICE: KEY : Key 3 VALUE : Value 3
DO

We use a subquery with the unnest function and the row_number() window function to locate the position (v_pos) of the key we wish to delete from the associative array. Then we call the array_rm function with the varchar_assoc_array, v_pos. The function removes the element at position v_pos and returns the updated array into a locally declared array. We assign the result of the array_rm function back to varchar_assoc_array to apply the deletion.

PostgreSQL approach using JSONB

First, we create a function named delete_item_json_map. This function takes a JSONB object (field_entity_map) and a key (key_item) as parameters. It returns a JSONB object with the specified key-value pair removed. See the following code:

CREATE OR REPLACE FUNCTION delete_item_json_map(
    field_entity_map jsonb,
    key_item text)
    RETURNS jsonb
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE    
BEGIN
   return field_entity_map-key_item;     
END;
$BODY$;

We use the following main logic to delete a specific key-value pair from the associative array using the delete_item_json_map function:

DO $$
DECLARE
field_entity_map jsonb  :='{}' ;
BEGIN
RAISE NOTICE 'Jsonb processed.';    
-- Populate the associative array with key-value pairs

field_entity_map = add_update_item_json_map(field_entity_map , 'Key 1'::text,'Value 1'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 'Key 2'::text,'Value 2'::text)::jsonb;
field_entity_map = add_update_item_json_map(field_entity_map , 'Key 3'::text,'Value 3'::text)::jsonb;
-- Delete an element into the associative array

RAISE NOTICE ' *** Delete a specific element from the associative array ***';

RAISE NOTICE 'before Delete varchar_assoc_array %',  field_entity_map;

field_entity_map=delete_item_json_map(field_entity_map,'Key 2');

RAISE NOTICE 'After deleting Key 2 from the associative array *** %',  field_entity_map;
END;
$$

Output:

NOTICE: Jsonb processed.
NOTICE: *** Delete a specific element from the associative array ***
NOTICE: before Delete varchar_assoc_array {“Key 1”: “Value 1”, “Key 2”: “Value 2”, “Key 3”: “Value 3”}
NOTICE: After deleting Key 2 from the associative array *** {“Key 1”: “Value 1”, “Key 3”: “Value 3”}

In this example, we called the delete_item_json_map function to delete the key ‘Key 2‘ from the field_entity_map JSONB object.

Clean up

Complete the following steps to clean up the resources you created in this post:

If you created a source RDS for Oracle DB instance, delete the instance via the Amazon RDS console.
Delete your target Amazon Aurora PostgreSQL-compatible edition or Amazon RDS PostgreSQL DB instance via the Amazon RDS console.

For more information, refer to Deleting a DB instance.

Conclusion

Migrating associative arrays from Oracle to PostgreSQL requires a thoughtful approach and consideration of alternative data structures and workarounds. PostgreSQL arrays and JSONB offer diverse options for handling key-value pairs. By understanding the differences between the two database systems and adopting the appropriate strategies, you can ensure a successful migration and maintain the desired functionality for managing associative data in your PostgreSQL environment. In this post, we showed you how to tackle the challenge of migrating associative arrays from Oracle to PostgreSQL. This process requires careful planning and consideration of various data structures and workarounds to ensure a successful transition. We discussed two primary options in PostgreSQL for handling key-value pairs, namely arrays and JSONB.

By understanding the distinctions between Oracle and PostgreSQL and adopting the right strategies, you can effectively migrate and maintain the desired functionality for managing associative data within your PostgreSQL environment.

We invite you to leave feedback in the comments section in this post.

About the Authors

Bikash Chandra Rout is a Lead Database Consultant with the Professional Services team at Amazon Web Services. Bikash focuses on helping customers build highly available, cost-effective database solutions and migrate their large-scale Oracle databases to AWS. He is passionate about databases and analytics.

Sashikanta Pattanayak is a Lead Consultant with the Professional services team at AWS. He works with customers to build scalable, highly available and secure solutions in the AWS cloud. He specializes in homogeneous and heterogeneous database migrations.

Neha Sharma is a Database Consultant with Amazon Web Services. With over a decade of experience in working with databases, she enables AWS customers to migrate their databases to AWS Cloud. Besides work, she likes to be actively involved in various sports activities and likes to socialize with people.

Gokul Gunasekaran is a Consultant with the Professional Services team at Amazon Web Services. He works as a Database Migration Specialist to help AWS customers migrate and modernize their on-premises database workloads to AWS cloud database solutions.

AWS Database Blog

Migrate an Oracle associative array to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL

Overview of Oracle associative arrays, PostgreSQL arrays, and Solution overview

Prerequisites

Iterate over an associative array

Oracle approach

PostgreSQL approach using an array

PostgreSQL approach using JSONB

Find the first and last elements and get the current size of an associative array

Oracle approach

PostgreSQL approach using array

PostgreSQL approach using JSONB

Find if an index exists in an associative array and update its index value

Oracle approach

PostgreSQL approach using array

PostgreSQL approach using JSONB

Insert a new element into the associative array

Oracle approach

PostgreSQL approach using array

PostgreSQL approach using JSONB

Delete an element from the associative array

Oracle approach

PostgreSQL approach using array

PostgreSQL approach using JSONB

Clean up

Conclusion

About the Authors

Resources

Blog Topics

Follow