Append integer number to integer array in postgres

Question

I am trying to add a new integer value to my existing integer array in postgres table. I am using array_append method here but getting "Cannot cast type jsonb to integer[]" error. My update query is as follows.

Note- I am just using it for learning purpose so I am intentionally saving phonenumber as integer.

update querytesting set jsondoc=jsondoc||jsonb_build_object(jsondoc->'PhoneNumber',array_append(((jsondoc->'PhoneNumber')::int[]),'6789')) where id=1;

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Sep 30, 2021 at 6:50

Laurenz Albe · Accepted Answer · 2021-09-30 06:17:46Z

1

You have to extract the array, append the new number, create a new JSON with the new array and store that. A tedious and inefficient procedure that would be unnecessary if you had stored your data in a normalized data model instead of a JSON.

UPDATE querytesting
SET jsondoc = jsonb_set(
                 jsondoc,
                 '{PhoneNumber}',
                 jsondoc -> 'PhoneNumber' || JSONB '[6789]'
              );
WHERE id=1;

answered Sep 30, 2021 at 6:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Append integer number to integer array in postgres

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