Postgres Array Append & Array Length for 'Array Push'
arrays, postgresql
Solution
The simplest thing would be:
update table
set array = array_append(array, 'element')
where ...
or perhaps use the `||` operator:
update table
set array = array || 'element'
where ...
Both of those are equivalent to the more common `set n = n + 11` for numbers. Depending on the types involved, you might need to disambiguate which `||` operator you mean by including a typecast:
update table
set array = array || 'element'::text
where ...
so that PostgreSQL knows you want the `array || element` version of `||` rather than the `array || array` version. Thanks to ak5 for bringing this to my attention.
Problem
What is the best way to add an element to an array when the size of the array is not provided? With `array_append` this is what I can think of: ``` UPDATE table SET array = array_append((SELECT array FROM table WHERE ...), 'element') WHERE ...; ``` With `array_length` this is what I can think of: ``` UPDATE table SET array[array_length((SELECT array FROM table WHERE ...), 1)+1] = element; ```