title: postgresql, jsonb field, array append via jsonb_set and jsonb_array_length
body: <p>I have postgresql with jsonb field that always contains array. 
  I need to append new values to that array or update already existing values by index.</p>
  
  <p>Looks like <code>jsonb_set</code> function meet my requirements. And for append new element i just need to max array index and update element with it.
  But i have a trouble doing this. Lets make it step by step.
  We have table campaigns with jsonb field team_members.</p>
  
  <pre><code> select id, jsonb_set(team_members, '{0}', '{"name" : "123"}') from campaigns;
   id  |     jsonb_set     
   -----+-------------------
   102 | [{"name": "123"}]
  </code></pre>
  
  <p>Okay great, if set path '{0}' statically everything works.
  Lets do that dynamically</p>
  
  <p>SQL for getting array length (it is our index for append)</p>
  
  <pre><code>  select  '{' || jsonb_array_length(team_members) || '}'::text from campaigns;
    ?column? 
    ----------
    {0}
  </code></pre>
  
  <p>Getting all together</p>
  
  <pre><code>   select jsonb_set(team_members, '{' || jsonb_array_length(team_members) || '}', '{"name" : "123"}') from campaigns;
  </code></pre>
  
  <blockquote>
    <p><strong>ERROR</strong>:  function jsonb_set(jsonb, text, unknown) does not exist
    LINE 1: select jsonb_set(team_members, '{' ||
    jsonb_array_length(tea...
               ^    HINT:  No function matches the given name and argument types. You might</p>
  </blockquote>
  
  <p>need to add explicit type casts.</p>
  
  <p>My question is - how can i get rid of this error ? What i'm doing wrong ?</p>
  
  <p>Thanks in advance.</p>