Is there any clean way to append items to a one-to-many relationship?

Question

Very innocent question

If we have an array of items in a table cell, is there any way to append data to it without having the need to fetch the data of the whole row to get its content, in order to finally push the updated data to the cell?

I have a column named content, which is an array of file identifiers, and I want to append some new data to it.

Is there any clean way to do this action? Or we need to perform a select() before an update()?

Could you share your code with us? It'd be much easier to give you advice — Roman Mahotskyi, May 27 '22 at 14:37
Please provide enough code so others can better understand or reproduce the problem. — Community, May 28 '22 at 07:08

score 0 · Accepted Answer · answered May 28 '22 at 02:23

It is generally better to create a separate table to store 1 to many relationships than to store them in an array, but with that being said, you could achieve appending items to array by using rpc() and calling your Postgres function.

You could create a function like the following to append array values:

create or replace function append_array(new_element text, id uuid)
returns void
as
$func$
    update array_table
    set array_element = array_append(array_element, $1)
    where id = $2
$func$
language sql;

You can read more about this here

Ok, thank you for your response, I consider setup that soon ! — chipseater_, May 28 '22 at 09:57

Is there any clean way to append items to a one-to-many relationship?

1 Answers1