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

Postgresql – Need to update row at Postgres table with join by array

my table schema looks this way:

create table table_with_arrays
(
    dim_col_code_array integer[] -- contains foreign keys to dict_table.array_code
    dim_col_val_array  varchar[] -- needs to be populated with dict_table.array_value
);

create table dict_table(
   array_code integer,
   array_value varchar
)

there is the field dim_col_code_array with values like [10,300,400]. these integers are foreign keys to dict_table.array_code.

For each row of table_with_arrays I need to join each value of dim_col_code_array with table dict_table by each element of dim_col_code_array = integer.array_code
And then store joined array_value in the same order to table_with_arrays.dim_col_val_array

Example:

table_with_arrays contains record:
[1,2,3], [/** fill me with values*/]

dict_table contains records
1 FIRST
2 SECOND
3 THIRD

query should update record of table_with_arrays to
[1,2,3], [FIRST, SECOND, THIRD]

is there any "smart" ideas / insights that I could try?

Tags:

2

Answers


  1. 0

    The join can be done using the operator ANY :

    SELECT dim_col_code_array, array_agg(array_value ORDER BY array_code) as dim_col_val_array  
FROM table_with_arrays t
JOIN dict_table d ON d.array_code = ANY(t.dim_col_code_array)
GROUP BY dim_col_code_array

    The update can be :

    UPDATE table_with_arrays t
SET dim_col_val_array = sub_q.dim_col_val_array 
FROM 
    (
    SELECT dim_col_code_array, array_agg(array_value ORDER BY array_code) as dim_col_val_array  
    FROM table_with_arrays t
    JOIN dict_table d ON d.array_code = ANY(t.dim_col_code_array)
    GROUP BY dim_col_code_array
    ) AS sub_q
WHERE sub_q.dim_col_code_array = t.dim_col_code_array;

    Demo here

    Login or Signup to reply.
  2. 0

    Unnest the array WITH ORDINALITY to remember the original order of array elements.
    Then join to the lookup table and feed the ordered result to an ARRAY constructor.
    Can be done in a correlated subquery:

    UPDATE table_with_arrays t
SET   dim_col_val_array = ARRAY(
                           SELECT array_value
                           FROM   unnest(t.dim_col_code_array) WITH ORDINALITY AS a(array_code, ord)
                           JOIN   dict_table d USING (array_code)
                           ORDER  BY a.ord
                           );

    fiddle

    See:

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

Back To Top
Search