Postgres query to update column value

Question

Column values in DB are something like this {1,2,4} & the value which I want to edit is currently null.

What postgres I can execute to make it similar to other value.

I've run a query but it has given undesired result: Result-> {[0:0]={1}} The query I've executed

update table_name set column_name[0]=1 where unique_val = 1;

After executing the query , I want row2-> value to get in similar structure to first row.

Any help would be appreciated.

For me it's completely not clear what you want to achieve. Please show us some sample data and the expected output. — S-Man
– S-Man, Commented Feb 25, 2021 at 11:31
{1,2,3,4} is not the same as row 1. Anyway can you say why this is not sufficient: UPDATE table_name SET column_name = '{1,2,3,4}' WHERE id = 2? — 404
– 404, Commented Feb 25, 2021 at 11:47

S-Man · Accepted Answer · 2021-02-25 11:58:26Z

1

demo:db<>fiddle

Simply updating with {1,2,3,4}:

UPDATE table_name
SET val = '{1,2,3,4}'
WHERE id = 2

Updating with the first record's value:

UPDATE table_name t
SET val = s.val
FROM (
    SELECT val FROM table_name WHERE id = 1
) s
WHERE t.id = 2

Addionally:

Please don't store array in your table. This makes everything much more complicated (searching and updating specific array elements) and less performant (joins on array elements require an unnest operation, indexing is not simply possible, ...). Please normalize your table design. That means there should be a relation table with columns table_name_id and array_element_id.

edited Feb 25, 2021 at 11:58

answered Feb 25, 2021 at 11:49

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres query to update column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related